I want to do the following:
Look-up a value in the top row of a table, and return the sum of certain rows below that value.
Simplified example:
A B C
Red Blue
1 John 3 1
2 John 4 2
3 Mike 2 5
I want to look-up "Red", and return the sum of all "Johns" in the Red Column.
I try this formula, but it doesn't work:
=HLOOKUP("Red",A1:C3,SUMIF(A1:A3,"John",B1:B3))
Any thoughts? Perhaps HLookup isn't the right function?
=HLOOKUP
Hi
Rgds
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Think the other way around...use a SUMIF but set summation range via INDEX, eg
=SUMIF($A$2:$A$4,"John",INDEX($B$2:$C$4,0,MATCH("Red",$B$1:$C$1,0)))
changing Red to Blue would alter summation appropriately
Last edited by DonkeyOte; 11-11-2009 at 09:19 AM. Reason: changed ranges to reflect existence of (inevitable) headers
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
interesting.
I'm not familiar with the Index function, what does that do?
Can you give me a bit more colour on this formula, because clearly it works, I just want to get a better sense of how to apply it to my far larger, more complex table.
Thanks so much!
In this instance the INDEX function is used to create a range, by setting row to 0 we ensure all rows are returned, we then specify for which column all rows are to be returned - this is determined by the match.
Examples of using INDEX in this context and result:
INDEX(A1:J10,0,4) --> D1:D10
INDEX(A1:J10,4,0) --> A4:J4
INDEX(A1:J10,4,4) --> D4
I'm still a bit confused, perhaps it's because I didn't use the right example to dictate what I'm trying to do.
I've attached a sample of the data I'm trying to summarize.
I was using HLOOKUP because I needed to look through the top row.
The major problem I find is that "Kids", "Women", and "Men", come up each time for each indicator.
Any thoughts?
Thanks so much!
Not sure whether Indicator1/2 is going to be a variable or not... based on your requirements and table you could use:
B22: =SUMIF($B$5:$B$14,$B$19,INDEX($C$5:$E$14,0,COLUMNS($B21:B21)))
copied across to D22
apply same to B23 but adjust INDEX range (from C:E to F:H)
The above could be made far more dynamic but as I say the final requirements remain a little unclear.
This is really helpful.
I have many many indicators (not only 2), is there a way to alter the formula so that each time I drag my formula down, it automatically moves the range from which it reads the data, rather than manually have to adjust INDEX range (from C:E to F:H)?
Thanks
Yes, but things will become a little more complex... again using your sample file:
orPlease Login or Register to view this content.
either of which can be applied across matrix B22:D23 simultaneouslyPlease Login or Register to view this content.
There are other options like using OFFSET or INDIRECT with named ranges but they either/or would be Volatile ... the above though more "fiddly" is a non-volatile approach (less calc overheads long-term).
Last edited by DonkeyOte; 11-12-2009 at 08:19 AM.
I was actually thinking of using offset, and hiding the values for the offset in a separate sheet.
Ok, I'm going to work off of this, and see where that gets me.
Thanks again!
In OFFSET terms
however as mentioned OFFSET is Volatile so though simpler on the eye it will calculate upon any (and every) volatile action undertaken in the file (if on auto calc - advised)Please Login or Register to view this content.
For more info. on Volatility see link in my sig. to Charles Williams' site, he being XL's calculation oracle.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks