Help!!!! Urgent My DSUM Formula Does not work.

[jamilm]

Dear Excel Gurus,

I have a table in one sheet and my DSUM formula in another sheet with the criteria range.

can you please look into the excel workbook uploaded https://skydrive.live.com/redir?resid=D7C00A2BF29043E0!229

I want the DSUM formula to return sum of MONETARY_AMOUNT column from the table called "Data" based on criteria of Fiscal Year Column to be 2012 only and Accounting Period to be 1, 2,3,4,5,6,7,8 then Account Column Any Account code with first three digists of 751*

=-DSUM(Data,Data[[#Headers],[MONETARY_AMOUNT]],A1:K2) it gives me an error of #Value i have an urgent report to prepare any quick help will greatly be appreciated thank you for your usual support and help. best regards, Jamil

<tbody>
</tbody>

 

[Norie]

I don't know why the DSUM doesn't work but you could use SUMPRODUCT, or SUMIFS if you have Excel 2007 or later.

=SUMPRODUCT(--(Data[FISCAL_YEAR]=2012), --(Data[ACCOUNTING_PERIOD]>=1), --(Data[ACCOUNTING_PERIOD]<=8), --(LEFT(Data[ACCOUNT], 3) = "751"), Data[MONETARY_AMOUNT])

=SUMIFS(Data[MONETARY_AMOUNT], Data[FISCAL_YEAR], 2012, Data[ACCOUNTING_PERIOD], ">=1", Data[ACCOUNTING_PERIOD], "<=8", Data[ACCOUNT], "=751*")

 

[jamilm]

I don't know why the DSUM doesn't work but you could use SUMPRODUCT, or SUMIFS if you have Excel 2007 or later.

=SUMPRODUCT(--(Data[FISCAL_YEAR]=2012), --(Data[ACCOUNTING_PERIOD]>=1), --(Data[ACCOUNTING_PERIOD]<=8), --(LEFT(Data[ACCOUNT], 3) = "751"), Data[MONETARY_AMOUNT])

=SUMIFS(Data[MONETARY_AMOUNT], Data[FISCAL_YEAR], 2012, Data[ACCOUNTING_PERIOD], ">=1", Data[ACCOUNTING_PERIOD], "<=8", Data[ACCOUNT], "=751*")

thanks very much for this Norie. however i still need to understand why the DSUM does not work. i have many database files that requires lots of formulas if not done by DSUM.
DSUM takes less time in terms of typing. i will wait and see if any other Guru would help on the DSUM.

 

[Norie]

You haven't set up the criteria correctly.

The accounting periods should be done the column, not across.

So you need to put 1- 8 in B2:B9, copy down the other values and change the criteria range in the formula.

Oh and you need Data[#All] not just Data

=-DSUM(Data[#All], Data[[#Headers],[MONETARY_AMOUNT]],Dsum!A1:D9)

 

[jamilm]

You haven't set up the criteria correctly.

The accounting periods should be done the column, not across.

So you need to put 1- 8 in B2:B9, copy down the other values and change the criteria range in the formula.

Oh and you need Data[#All] not just Data

=-DSUM(Data[#All], Data[[#Headers],[MONETARY_AMOUNT]],Dsum!A1:D9)

many thanks. i have two question. first one why did you put a minus before DSUM formula ? second question is that when i used pivot table to get the data with same criteria it give me a total of 12529.29 whereas the Dsum updated formula as per your advise gives me a different total of 678773.5

i have re uploaded the workbook with the pivot and revised formula. i would greatly appreciate it if you could give it a look and tell me where the problem is.
https://skydrive.live.com/redir?resid=D7C00A2BF29043E0!231

many thanks.

 

[Norie]

Your original formula had a minus.

The discrepancy between the results is because you haven't copied down 2012 and 751* as I suggested.

They should both be copied down to row 9.

When you do that the DSUM value matches that in the pivot table.

 

[jamilm]

Your original formula had a minus.

The discrepancy between the results is because you haven't copied down 2012 and 751* as I suggested.

They should both be copied down to row 9.

When you do that the DSUM value matches that in the pivot table.

Dear Norie,

You hit the nail on the head. Excellent ! Thanks a Million. it worked perfectly now and made my life easy by creating my reports on the fly

Best regards,
Jamil