SUM IF

[Duey on the lake]

I use Excel 2000.


Column A is formatted for dates
Column B has numerical entries.

I want to sum column B, IF the dates in column A are for a specific month.

 

[Roger Govier]

Hi

Try
=SUMPRODUCT((TEXT($A$1:$A$1000,"yymm")="0812")*$B1:$B1000)

It would probably be better to put the Year and Month in a separate cell,
then you can just change the value to retrieve data for other months e.g.
with 0812 in cell D1

Note you cannot use whole columns as arguments in Sumproduct. Change the
range to suit but ensure both ranges are of equal dimension.

 

[Mike H]

Hi,

Try this with the month number in c1

=SUMPRODUCT((MONTH(A1:A10)=C1)*(B1:B10))

You may also need to specify a year in D1

=SUMPRODUCT((MONTH(A1:A10)=C1)*(YEAR(A1:A10)=D1)*(B1:B10))

Mike

 

[Shane Devenshire]

Hi,

Here are modifications to your recieved suggestions:

=SUMPRODUCT(--(MONTH(A1:A59)=1),B1:B59)

=SUMPRODUCT(--(TEXT(A1:A59,"m")="1"),B1:B59)

1. Although it probably doesn't make any difference with your data, using
the -- handles a number of issues that can come up.
2. If you really want to base the sum only on month and not month and year
then the second formula usable.
3. If you really want the month and the year then you would need to modify
the first formula to something like:

=SUMPRODUCT(--(MONTH(A1:A59)=1),--(YEAR(A1:A59)=2009),B1:B59)
or the shorter (but more problematic) form

=SUMPRODUCT((MONTH(A1:A59)=1)*(YEAR(A1:A59)=2009)*B1:B59)

Instead of 1 and 2009 in the formulas you should use cell references. If
you want to use month text such as Jan then the second formula converts
easily to

=SUMPRODUCT(--(TEXT(A1:A59,"mmm")=D1),B1:B59)
where D1 contains Jan

The other formulas can use text references also for example,

=SUMPRODUCT(--(TEXT(A1:A59,"mmm")="Jan"),--(YEAR(A1:A59)=2009),B1:B59)