MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Another Sum Question


Posted by Mike on October 02, 2001 2:49 PM

I have the following:
Cell A1 = 08/15/01
A2 = 09/01/01
B1 = $100
B2 = $200
C1 = 1000
C2 = 1500
D1 = 08/01/01
D2 = 08/31/01

Column A is a transaction date, B is a dollar amount and C is a project code. D is where the date range I am looking for is input. In A4 i want to be able to sum the transactions that occured during the date range in column D for project #1000. The result should be $100 because that is the only transaction for that project. I dont know if this needs a combination of SUMPRODUCT and SUMIF. Any help would be appreciated.


Posted by IML on October 02, 2001 2:54 PM

You just need to add one more argument to Aladin's formula,

=SUMPRODUCT((A1:A3>=D1)*(A1:A3<=D2)*(C1:C3=1000)*(B1:B3))
the 1000 can be replaced by a cell reference, such (C1:C3=D3) if you wish.

good luck

Posted by Mark W. on October 03, 2001 6:08 AM

A minor technicality...

Aladin's SUMPRODUCT formulation only has 1 argument.
Arguments are separated by commas rather than
mathematical operators (See the Excel Help
topic for "About using functions to calculate
values"). You just added another "term" to an
"expression". I know... I know... I'm splitting
hairs, but I feel that it's important to get the
terminology right for those just getting started
with Excel. : )

Thank you for your contributions!!

Posted by IML on October 03, 2001 9:04 AM

Its always better to get it right...thanks. (nt)