DSUM with non-adjacent criteria-columns

[MichaelDam]

Hi there,


Is this formula possible….??

Criteria
Account Month
=Dsum(range;field; 5030 10 )


Where Account may be at F5 and Month at G16


or is there a work-around..??

Best regards
Michael

[loginjmor]

Hi -

I'm not sure I understand your question. The syntax you posted does not appear correct to me, but I can't really tell what you are doing. Can you upload a spreadsheet to this forum of what you are trying to do and perhaps a little more explanation?

Thanks.

[MichaelDam]

Sorry, the pasted formula looked OK before I posted.

Basically I'm asking: If I use the DSUM formula, is it possible to write CRITERIA directly in the formula cell. Problem is that I haven't got room for a criteria as it will fill two lines and I have a matrix of app. 200 X 15 cells I need to have a DSUM on. Further I have two criterias that have to be true to get my figure. System breaks down when I'm trying to attach.

Accounts are on one sheet - ascending order
Postings such as account, month, amount is on an other sheet
I need DSUM per account per month

Did this clarify??


Thanks in advance!!
Michael

[MichaelDam]

Finally managed to upload without crashing!!

This is the testsheet... hope it is useful!!?

BR
Michael

[loginjmor]

Hi -

I think what you are trying to do would be easiest with a Pivot Table. I have attached a spreadsheet that includes a pivot table that does just what you are looking for. I added a column on your SHEET2 data set to calculate just the month so the pivot table can sort and sum by month rather than specific transaction date. Just go to the Pivot Table sheet I added, click anywhere in the pivot table, and it will bring up the Pivot Table Window so you can see how I dragged the various fields to create a pivot table similar to your desired ENDproduct. Read up on pivot tables. I really think that will be a better and more flexible solution than programming DSUM or SUMPRODUCT.

Hope this helps.

[MichaelDam]

I can't thank you enough....!! I've spent hours searching the web for a solution, but obviously been looking in the wrong direction!

THANKS A LOT!!!


Michael

[Kevin UK]

Hi MichaelDam

In F28 you can use the SUMPRODUCT copy across and down to Q37, format the cells as "#,###;;"

Please Login or Register  to view this content.

[MichaelDam]

Hi Kevin,

You're absolutely spot on, but I think I will stick to the Pivot Table as it gives me the option to omit empty accounts in my final report!

Thanks for the effort!!


BR
Michael