Conditional Sums? (1 Viewer)

AC5FF said:

I really should know the answer to this; but I cannot for the life of me get things to work! Hopefully someone here will know the answer and give me a virtual slap across the face!

I have two columns of data here: One is location (DAV/REW/OTHER) the second is an amount (Dollar Figure). What I want to do is sum all the amounts if it is a DAV amount. Likewize I'll need to do the same with REW and OTHER amounts, but just getting the first one to work will let me know how to do the others...

I tried to use the VLOOKUP function, but it will only return the first line that it finds with "DAV". Any other ideas?

Sample of data:
DAV...$2
DAV...$3
REW...$2
DAV...$9
OTHER...$8
REW...$2
DAV...$3

THANKS!

Click to expand...

[B]SELECT[/B] TheTable.ItemType, [B][COLOR=#00bfff]SUM([/COLOR][/B]TheTable.ItemCost[B][COLOR=#00bfff])[/COLOR][/B]
[B]FROM[/B] TheTable
[COLOR=deepskyblue][B]GROUP BY[/B][/COLOR] TheTable.ItemType;

AC5FF said:

MSAccessRookie: I either am not fully understanding you here or you mis-understood my question. Your reply looks like it would work in Access; but not Excel (I could very well be wrong here!) If I had this in Access I don't think it would have been a problem; but since it is an Excel spreadsheet... Just giving me fits

Mike375: Thank You! You can virtually slap me across the face!
SUMIF(range,criteria,sum-range) DUHHHH!!!! I totally spaced that one! I was trying a SUM(IF(VLOOKUP(..............))) and just digging a hole deeper and deeper. Your link fixed it all right up for me! Thanks!

Click to expand...