SUMPRODUCT looking at date & month

[Chuck]

hi guys,

i have a worksheet that looks at purchases i have made and the
sumproduct calculation should look at the date of invoice (tabs
01-13). however, i can only get the sumproduct to just look at the
month, but i also need the year

here is my formula

=SUMPRODUCT(--(MONTH(POSummary!$I$3:$I$171)=MONTH(I$8)),--(POSummary!$C
$3:$C$171=$B78),(POSummary!$G$3:$G$171))+SUMPRODUCT(--
(MONTH(Contractors!$F$3:$F$9999)=MONTH(I$8)),--(Contractors!$E$3:$E
$9999)*1)

can anyone advise how to accomplish this? i tried doing

=SUMPRODUCT(--(MONTH(POSummary!$I$3:$I$171)=MONTH(I$8)),--
(YEAR(POSummary!$I$3:$I$171)=YEAR(I$8)),--(POSummary!$C$3:$C$171=$B78),
(POSummary!$G$3:$G$171))+SUMPRODUCT(--(MONTH(Contractors!$F$3:$F
$9999)=MONTH(I$8)),--(Contractors!$E$3:$E$9999)*1)

but i keep getting an error

cheers
chuck

 

[Dave Peterson]

If your first formula worked ok, then adding the year() shouldn't cause a
problem. You may have non-numeric (non-date) date in those fields. Or maybe
you have an error in one of the ranges?

ps another way of doing your formula:

=SUMPRODUCT(--(text(POSummary!$I$3:$I$171,"yyyymm")=text(I$8,"yyyymm")),
--(POSummary!$C$3:$C$171=$B78),
(POSummary!$G$3:$G$171))
+SUMPRODUCT(--(text(Contractors!$F$3:$F$9999,"yyyymm")=text(I$8,"yyyymm")),
--(Contractors!$E$3:$E$9999)*1)

ps. If you have text values in E3:E9999 that look like numbers, you may want to
fix them. I've found that if I adjust a formula to "correct" the data, I'll
forget to do it somewhere else--or a different user will forget to do it.

 

[Chuck]

hi Dave,

thanks for your input. i found the error was just incorrect syntax on
my part. i have reconfirmed that those values are date format just to
be sure.

this is my new formula which is working

=SUMPRODUCT(--(MONTH(POSummary!$I$3:$I$171)=MONTH(I$8)),--
(YEAR(POSummary!$I$3:$I$171)=YEAR(I$8)),--(POSummary!$C$3:$C$171=$B78),
(POSummary!$G$3:$G$171))
+SUMPRODUCT(--(MONTH(Contractors!$F$3:$F$9999)=MONTH(I$8)),--
(YEAR(Contractors!$F$3:$F$9999)=YEAR(I$8)),--(Contractors!$E$3:$E
$9999)*1)

=============
another off topic though, can i do an IF range then a sumproduct?

eg, =IF('Purchase Record'!G6:G18>1,SUMPRODUCT(--(MONTH(POSummary!$I
$3:$I$171)=MONTH(I$8)),--(YEAR(POSummary!$I$3:$I$171)=YEAR(I$8)),--
(POSummary!$C$3:$C$171=$B78),(POSummary!$G$3:$G$171)),0)
+SUMPRODUCT(--(MONTH(Contractors!$F$3:$F$9999)=MONTH(I$8)),--
(YEAR(Contractors!$F$3:$F$9999)=YEAR(I$8)),--(Contractors!$E$3:$E
$9999)*1)

i need to look at a column to see if any relevant dates are >1, if so,
calculate, if not, then 0

the above is a general thing, but i dont know how to do IF Range
SUMPRODUCT. or does that make sense?

also, any reason why the for the correct formula (or yours) seems to
be doing double calculation? the numbers are increasing though they
should not

here is the sample of output (per month)
$27,600.00 $115,000.00 $164,000.00 $- $295,000.00
$308,000.00

'technically, the average should be about 30k per month, but i cant
figure out why the calculations are increasing every month

or should i post this on a new topic?

cheers again

 

[Dave Peterson]

You can use an if statement.

But I'm not sure what g6:g18>1 means. Does it mean that there's at least one
value greater than 1 or all 13 values are larger than one or what???

=if(min('Purchase Record'!G6:G18)>1, ...
=if(countif('Purchase Record'!G6:G18,1)=13, ...

ps. I find using the =text() version easier to read.