Hi,
=SUM(OFFSET($C$6,COUNT(C:C)-5,0,5))
With that formula I get last 5 values in column C.
What I need: last 5 values for a specific name in column B.
For example: I only want it to count last 5 values in C when B says BART.
Thanks advance!
Hi,
=SUM(OFFSET($C$6,COUNT(C:C)-5,0,5))
With that formula I get last 5 values in column C.
What I need: last 5 values for a specific name in column B.
For example: I only want it to count last 5 values in C when B says BART.
Thanks advance!
Do you mean.
=IF(B6="BART", your old formula , "")
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
No, that doesn’t work.
I want last 5 values from column C for value ‘Bart’ in column B
Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).
1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!
2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.
4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Do you mean.
=SUMIF(INDIRECT("B"&AGGREGATE(14,6,ROW(B:B)/(1/(B:B="BART")),5)&":B"&LOOKUP(2,1/(B:B<>""),ROW(B:B))),"BART",INDIRECT("C"&AGGREGATE(14,6,ROW(B:B)/(1/(B:B="BART")),5)&":C"&LOOKUP(2,1/(B:B<>""),ROW(B:B))))
Regards.
Ps. This is not a good formula because it's use AGGREGATE with full range of column, you may get a better formula from others member later.
As attachment the file you asked for.
The approximate number of rows of data I want to work with: 100000
Thanks alot
Adjust the ranges to suit your data:
=SUMPRODUCT(($B$6:$B$31=F7)*(ROW($6:$31)=LARGE(($B$6:$B$31=F7)*ROW($6:$31),{1,2,3,4,5}))*$C$6:$C$31)
You may need ; and not , as the separator.
Please try.
G7
=SUM(INDEX(C:C,N(IF(1,AGGREGATE(14,6,ROW(B:B)/(1/(B:B=F7)),{5,4,3,2,1})))))
Regrads.
[QUOTE=Glenn Kennedy;5204253]Adjust the ranges to suit your data:
=SUMPRODUCT(($B$6:$B$31=F7)*(ROW($6:$31)=LARGE(($B$6:$B$31=F7)*ROW($6:$31),{1,2,3,4,5}))*$C$6:$C$31)
You may need ; and not , as the separator.[/QUOTE
When I change C into D in the formula it doesn’t count the values in D. How’s that? Need this because I have more than one column of data. Thought it would be simple like just change the range where he needs to count the values :-(
I'm not sure that I understand you. Please post a amended sheet showing the problem.
Thx again for your effort
Does your real data have missing values?? By that, I mean the blank cells in D6:D26, etc?
Never mind.
Try this:
=SUMPRODUCT(($B$6:$B$31=$H7)*($C$6:$C$31<>"")*(ROW($6:$31)=LARGE(($B$6:$B$31=$H7)*(C$6:C$31<>"")*ROW($6:$31),{1,2,3,4,5}))*C$6:C$31)
It now ignores blanks, if there are any.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks