Excel Conditional Sum

[james]

I am trying to add items delivered to our facility by
month. I have column a listing dates by day (date format:
7/14/2004) in column A on a worksheet tab called 'daily
activ', column B lists the number of items delivered each
day. On another worksheet tab called 'growth' in the same
document I have the various months in column A (June, July,
etc). In column B I would like to apply a formula pulling
from the 'daily activ' tab to sum items for only for that
month. I would also like the formula to use a wildcard so
it only looks at the month and year. I tried applying the
follwoing using Ctrl+Shift+Enter, but it gives me a zero sum:

=SUM(IF('daily activ'!$A$2:A20000="6/*/2004",'daily
activ'!$B$2:$B$20000,0),0)

I am not quite sure of the role/need/placement of the last
two zeros in the formula as I was working from a formula I
got from someone else. Any help or ideas appreciated. James

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(MONTH('daily activ'!$A$2:A20000)=6),--(YEAR
('daily activ'!$A$2:A20000)=2004),'daily activ'!
$B$2:B20000)

 

[Andy B]

Hi

Try something like:
=SUMPRODUCT((daily activ'!$A$2:A20000>=DATE(2004,6,1))*(daily
activ'!$A$2:A20000<=DATE(2004,6,30))*('daily activ'!$B$2:$B$20000))

 

[james]

Thanks, worked like a charm; I had no idea of the double
dashes and plain text MONTH YEAR references in excel, it
also is easy to follow what the formula is trying to do.

I also tried Andy's suggestion:
=SUMPRODUCT(('daily
activ'!$A$2:$A$20000>=DATE(2004,6,1))*('daily
activ'!$A$2:$A$20000<=DATE(2004,6,30))*('daily
activ'!$B$2:$B$20000))
I got reference errors. I tried to tweak it some but my
understanding of excel functions and formulas is not strong
enough.

Thanks much Andy and Frank, this will save me hours.