Excel 2007 : DSUM on multiple sheet

**aaslezak** · 09-28-2009, 11:52 AM

I am trying to do a Dsum on multiple sheets I named my first table January so when I use the Dsum on the sheet Sales Summary work sheet then I have criteria on the sales summary for equal to P in shipping and Y In State the formula looks like this =DSUM(January[#ALL], January[Gross Sales],G16:H17) and it keeps coming up with #VALUE!. I have tried various methods to fix this to no avail. Any suggestions would help.

**NBVC** · 09-28-2009, 12:06 PM

The Field value (middle condition) should be the single cell January!E4.. which tells Excel with Field (column) to add...

e.g

=DSUM('January Sales'!$A$4:$K$50,'January Sales'!E4,G16:H17)

Note: G21 in the Summary table has an extra space after the "S".. please remove or you will get 0 result.

Check all others for this inconsistency too.

**aaslezak** · 09-28-2009, 12:34 PM

Well that did it and it worked on my daverage values too thanks for the tip on how to use the DSUM on different sheets.

Al

**NBVC** · 09-28-2009, 01:15 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**aaslezak** · 10-01-2009, 03:52 PM

That was not the correct solution I needed to use the names values for the field and if I use "Gross Sales" instead of 'January'!E4 then the values come out correctly. Thanks for the help

Al