Hi all -
What would be the correct syntax to convert this sumproduct formula to a dsum formula
I can't seem to find any examples that match my exact need
=SUMPRODUCT((Data!$A$2:$A$15998=$B$3)*(Data!$C$2:$C$15998=$A14)*(Data!$E$2:$E$15998=B$10)*(Data!$D$2:$D$15998))
Thanks much
-marc
Re: Convert Sumproduct Formula To Dsum
The database functions are very easy to use. The Excel help has lots of examples as does the SUMPRODUCT auto link under Multi Criteria Sum.
Re: Convert Sumproduct To DSUM
=DSUM(YourDataTableIncludingHeaderRows,TheHeaderCellThatYouWantToSum,YourCriteria)
Send me an example copy of your book and I'll show you if you like
Lee
Re: Convert Sumproduct To DSUM
Thanks Guys -
I tried downloading the DSUM samples, I guess I can't figure out how to use with my current worksheet setup.
I've attached a small sample of the workbook
The current sumproduct formula is in cell B15
I formatted the criteria cells a blue interior
Thanks much
-marc
Re: Convert Sumproduct To DSUM
Hi Marc,
Yeah it was a bit fiddly with your current worksheet set up, yet I'm no expert! The thing with the DSUM etc is that you need to set a criteria for each formula, so you may find your SUMPRODUCT formulas work just as well.
The sheet entitled "Summary" holds the finished results, but lots of the formulas etc are hidden.
The sheet "Summary showing all formulas" is identical except you can see how it's all set up.
Also, I set up a Dynamic Range called "All_The_Data" that refers to the data on your "Data" sheet so that it expands automatically should you add extra rows or columns. It assumes that there will always be a value in column A.
I played around with some of your data on the "Data" sheet too, just for testing purposes.
Anyway. Hope this helps. I learnt a few things while I was doing it so it was useful to me too!
Cheers
Lee
Re: Convert Sumproduct To DSUM
Consider a PivotTable.
Re: Convert Sumproduct To DSUM
Thanks much Lee!
I see what you are saying that sumproduct may be the way to go
At least I understand DSUM better now and can incoporate in other worksheets
Thanks again for your assistnace and hard work!
-marc
Re: Convert Sumproduct To DSUM
No problem Marc
Don’t have an account yet? Register yourself now and be a part of our community!
