Ok, so, I want to add up a number based off of the numbers that correspond to different criteria from different worksheets in excel. I tried using DSUM, but to no avail. Here's a sample example of what I have:
Fall '07 (name of Sheet 1):
Name Field Date Credits
blah Jap blah 5
blah Eng blah 3
Spring '08 (name of Sheet 2):
Name Field Date Credits
blah Jap blah 5
blah Eng blah 4
So, basically I want excel to add up all the credits for a specific field, say Japanese, then again for English, etc... I want this in a brand new worksheet. There are more than 2 worksheets and two items per, but you get the idea. What formula can I use? Or, maybe, how can I enter the range for DSUM pulling from multiple worksheets?
Also, how would I be able to generate a grand sum total without any criteria narrowing down the data?
Last edited by DaveJS; 07-04-2008 at 09:35 PM.
Have you tried SUMIF or SUMPRODUCT?
Ed
_____________________________
Always learning, but never enough!
_____________________________
Hi, and welcome to the forum,
Unfortunately =DSUM() is one of the functions that doesn't accept a 3D range, i.e. a range which spans worksheets.
Probably the easiest solution as follows. It assumes that your data is consistently laid out on the data sheets, (i.e. field headings are common and always in the same range), and that all the data sheets are grouped next to each other with, in this case,Fall 07 being the first sheet and Fall 08 being the last sheet. In your real world, just change the 'Fall 08' reference in the summary formula to whatever is your last sheet name.
First add the four field headings as a criteria range in A1:D4 on sheet 3, and enter say 'Jap' in B2
Now enter
=SUM('Fall 07:Fall 08'!F1)
in say F1 on sheet3
and now enter
=DSUM(A1:D10,"Credits",Sheet3!A1:D2)
In F1 on each of the data sheets
HTH
Thank you very much!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks