DSUM - SUMIF - or something else?
Thank you very much. This is just what I was looking for.
"Bob Phillips" wrote:
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" wrote in message
...
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
.
|