I'd like to SUM all values in DATA TABLE col_b where DATA TABLE col_a has a value of MED in the LOOKUP TABLE.
For example using the tables below..
I want to sum col_b in the data table for all Cat# that have a lookup of HIGH. This would be Cat1, Cat2, and Cat5. Thus, equalling: 5+3+9+8 = 25
Any ideas on how to get this to work?
Please Login or Register to view this content.Thanks,Please Login or Register to view this content.
JC
Last edited by carlyman; 08-18-2011 at 09:10 AM. Reason: Showing that lookup table will be unordered
If you list your Lookup_table with column a sorted alphabetically as per your sample, then....
Assuming Lookup_table is in A1:B6, incl. headers and Data_table is in D1:E9, then try:
=SUMPRODUCT(--(LOOKUP($D$2:$D$9,$A$2:$B$6)="High"),$E$2:$E$9)
you can replace "High" with a cell reference.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Why does the list need to be ordered alphabetically? I tried it w/o arranging it that way and get #N/A, so it is obviously necessary.
Unfortunately, the list must be sorted in a non-alpha and non-numeric order. So...any other potential way? I know I can always add a column to the data table and do a look up there, but really trying to avoid it.
Thanks again,
JC
LOOKUP() is one of the few functions that will allow you to build an array of text results within a formula so that you can compare and match to other tables... Lookup() unfortunately requires lookup data to be listed in sorted ascending order to work properly.
I am trying to think of something else, but it is late in the day and my brainpower is going fast... I see other eyes on this thread and perhaps they will have something that will not require a helper column....
Okay give this a try.. with the same assumptions, except lookup table doesn't need to be in order....
=SUMPRODUCT(--(ISNUMBER(MATCH($D$2:$D$9&"High",$A$2:$A$6&$B$2:$B$6,0))),$E$2:$E$9)
again, you can replace "High" with a cell reference
Hello carlyman,
See the attached with DSUM formula.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
@Haseeb: Thanks for the spreadsheet. It works, but I don't quite understand the limitations on what rows the information is on. If I cut and paste the lookup table down or insert a row at the top, the result is no longer valid.
@NBVC: That works!!! In my actual task tho, I need to add one more filter (possibly two). There is another column in the data table that has a "type" field; using your example, make it column F. So, I attempted to just do the following:Please Login or Register to view this content.
I also tried multiplying instead of using a comma to separate the arrays, but that did not work either.
Am I stretching the limit of Excel too much now? Very much appreciate your thoughts.
Thanks again,
JC
I would try:
for better robustness, perhaps add a special symbol between so that there is no accidental overlaps...Please Login or Register to view this content.
e.g.
The underscore makes definitive separations in the words in case there may be an dubious overlap.Please Login or Register to view this content.
NBVC,
Thanks for the quick input....something is not quite adding up right in my actual task, so give me a bit to try and debug. At least I understand your recommended change
-Jordan
I assumed the column F: Type is in the Lookup_Table....
Also that you are looking for the word Type in column F in order for it to be a match....
@NBVC: Sorry for my slow response, my work network seems to keep going down and can't access outside websites.
But, I just read your last post and realized I probably wasn't clear. The "Type" is in the data table and has no correlation to the lookup table. It would be just a "normal" SUMPRODUCT array function.
By normal, I mean (ignoring my lookup issue), the formula would be:
=SUMPRODUCT((F2:F6)="TypeA")*E2:E6)
My problem is I need to do the above filter together with the lookup function you provided. I can't quite figure out how to combine them.
Thanks again for your help,
JC
Can you repost a sample workbook with the added information and expected results?
Attached is a sample file...hope it helps.
-JC
Try, in H11:
=SUMPRODUCT(--(ISNUMBER(MATCH($A$12:$A$21&F11,$A$2:$A$6&$B$2:$B$6,0))),--($B$12:$B$21=G11),$C$12:$C$21)
copied down
Awesome...let me try this out in my actual work, but the question as asked is solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks