Professional Excel Development 2nd Edition

[xld]

When you know the syntax, you can do it in 10 seconds without helper column.
=SUMPRODUCT(--(MONTH($B$7:$B$81)=MONTH(DATEVALUE(1&"/"&B84&"/"&1))))

Much simpler to ditch the DATEVALUE stuff

=SUMPRODUCT(--(MONTH($B$7:$B$81)=B84))

(will give #VALUE if some cell is not a date: to avoid the problem you could add ISNUMBER)

That won't work, you need to outsort the non-dates with an SUM(IF array formula.

 

[Greg Truby]

Took me maybe 15-20 seconds. Of course I just used a pivot table.

BTW - I still can't seem to figure out that the heck to use a blankety-blanking COUNTIF.

• =COUNTIF(B$7:B$81,MONTH(B$7:B$81)=B84)

Didn't work at all. Me and COUNTIF and SUMIF just don't seem to get along.

 

[xld]

Took me maybe 15-20 seconds. Of course I just used a pivot table.

BTW - I still can't seem to figure out that the heck to use a blankety-blanking COUNTIF.

• =COUNTIF(B$7:B$81,MONTH(B$7:B$81)=B84)

Didn't work at all. Me and COUNTIF and SUMIF just don't seem to get along.

That is why SUMPRODUCT is so popular.

 

[QuietRiot]

Sweet.. I bought this book on pre-order at barnes & Noble.

 

[Hongbin]

it took me 4mins.
=SUMPRODUCT((MONTH($B$7:$B$81)=B84)*1)

I usually think with some knowledge of matrix manipulation plus knowing true =1 and false=0, it would solving this type of problems a lot easier.

Erick, can you please enlighten me what the double minus means in your formula.

Cheers,

Hongbin

 

[xenou]

Question:

Erick, can you please enlighten me what the double minus means in your formula.

Answer:

SUMPRODUCT() ignores non-numeric entries. A comparison returns a boolean (TRUE/FALSE) value, which is non-numeric. XL automatically coerces boolean values to numeric values (1/0, respectively) in arithmetic operations (e.g., TRUE + 0 = 1).

The most efficient way to coerce the value is first to apply the unary minus operator, coercing TRUE/FALSE to -1/0, then applying it again to negate the value, e.g., +1/0.

The array is then numeric and will be evaluated by SUMPRODUCT().

FROM: http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[xenou]

Wow, I must be a excel super guru. Took me -8 seconds! Or maybe I have difficulties with copy/paste values? In reality, I put my -- in the wrong place and burned a minute and a half debugging my formula. Wish I'd gone with countif after all...which, BTW, is speedily handled by adding a column next to dates to get the month, and then using count if directly on the helper column. I've never gotten countif to work with much more than primitive conditions (a la Greg's post) either.

 

[Hongbin]

Hi Alexander
many thanks for your answer.
the array formula below seems to be working now.
{=SUM(--(MONTH($B$7:$B$81)=B84))}

******** type=text/javascript> vbmenu_register("postmenu_1932241", true); *********>

 

[SydneyGeek]

Another option:

Starting a new column in row 1 and filling down to 12, you can use

=SUMPRODUCT(--(MONTH($B$7:$B$81)=ROW()))

Took me about 40 secs (I'm a slow typist)
Pivot took about 25...

Denis

 

[isoke81]

A B C
1 45 01/01/2009 E01/03
2 60 04/01/2009 E04/03
3 70 06/02/2009 E02/03
4 A0009 A0009 A0009

For senario A if I were to use sumproduct via the following formula =SUMPRODUCT(--ISNUMBER($A$1:$A$491),$A$1:$A$491) my answer would be 175

For senario B is there any way i can use sumproduct function to calculate the number of months in January that would give me an answer of 2?

I tried to use the following formula =SUMPRODUCT(--ISNUMBER($A$1:$A$491)*(MONTH($A$1:$A$491)=1)) but this gives me a #VALUE instead of 2.

For senario C, how do I go about on the sumproduct using the numerical 4 as an identifier to sum the said column? The answer for the above is 3.

This Many thanks in advance