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! :D

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!

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! :D

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!


I would think that SUM() and GROUP BY would work here.

SELECT TheTable.ItemType, SUM(TheTable.ItemCost)
FROM TheTable
GROUP BY TheTable.ItemType;


Methinks I need to be more observant. This is an Excel Issue, not an Access one. I need more Coffee.

This might be what you want as I posted up what was similar to your question. The poster who answered was also on this forum but I don't think he is around anymore. He was real Exc el whiz kid:D

This is a link to a thread I started on dbForums

http://www.dbforums.com/showthread.php?t=1626947

I think the attached Excel file (in that thread) should still work, if not I have it here somewhere.

PS,

I just found the Excel file where I did it. On Sheet 4 and referencing Sheet 1.

My one glorious moment in Excel:D

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! :D
SUMIF(range,criteria,sum-range) DUHHHH!!!! :rolleyes: 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!

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! :D
SUMIF(range,criteria,sum-range) DUHHHH!!!! :rolleyes: 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!

Yes, it works for Access and not for Excel. I already spanked myself for not reading well enough.

A solution from almost a year ago. Wow, the last time I was called a "kid" was about a half century ago. :D

You are right, Mike. I seldom get by here with my new position (seminary president). But occasionally I miss the old crowd and peek in to see what is going on.