DSUM - SUMIF - or something else?

[MAPepin]

OK - Here's the problem.

I have two worksheets named Totals and Data.

In Data there are three columns, as follows:

A:A B:B C:C
Date Desig Amount

The Date column is formatted for DATE (d-mmm-yyyy); the Desig column is
General formatted with a drop-down list to pick from (Water, Sewage, etc.);
the Amount column is formatted for currency.


In the Totals worksheet, C3:N3 contain dates (m/d/yyyy), B6:B17 contains
the row names corresponding to the names in the Data!B:B (Desig) drop-down
lists, and the range C6:N17 will contain the formula I seek.
I'll just work with cell C3 for now.

Let's start off stating that B6 = "Water" and C3 = 10/1/2007

Here is the logic of what I want to do:

IF the MONTH in Data!A:A = the MONTH in C3,
AND the YEAR in Data!A:A = the YEAR in C3,
AND the text in Data!B:B = the text in B6 (Water)
then
SUM the amounts in Data!C:C


Basically, I only want to sum the numbers if the month, year, and designator
match. I thought that this would be easy but it's kicking my but!

FRUSTRATED

 

[Teethless mama]

=SUMPRODUCT(--(MONTH(A2:A100)=MONTH(C3)),--(YEAR(A2:A100)=YEAR(C3)),--(B2:B100=B6),C2:C100)

 

[Bob Phillips]

I would have suggested DSUM, but the layout of the Totals sheet works
against it.

Try

=SUMPRODUCT(--(Data!$A$2:$A$200-DAY(Data!$A$2:$A$200)=C$3-DAY(C$3)),--(Data!$B$2:$B$200=$B6),Data!$C$2:$C$200)

and copy across and down.


HTH

Bob

 

[MAPepin]

Thank you very much. This is just what I was looking for.