CONCATENATE and SUMPRODUCT in a formula??

[Guest]

The followign formula returns a #VALUE! error:
=CONCATENATE(SUMPRODUCT(--(C$2:C$996="Aug"),--(YEAR(D$2$996="2006"))," 2006
August transactions"))

The intent here is to count all August 2006 transactions and display the
count as a text string: "XX August 2006 transactions"

Seems like it is pretty simple to do this and that the above formula should
do this. But it doesn't. So, thoughts?

C2:C996 is calculated off D2996 with the formula
=IF(ISBLANK(H2),"",TEXT(D2,"mmm")) filled down to the end of the range. (H2
is a transaction amount or else blank.)

D2996 is a date, formatted mm/dd/yy, as in 8/14/2006

Here's the weird part: when I change the formula to
=CONCATENATE(SUMPRODUCT(--(C$2:C$996="Aug"),--(B$2:B$996="2006"))," 2006
August transactions") the count returned is 0. In this case B2:B996 is
calculated based on the formula =IF(ISBLANK(H2),"",YEAR(D2)). See above for
an explanation of H2 and D2.

Finally, =CONCATENATE(COUNTIF(C$2:C$996,"Aug")," August transactions") works
perfectly. The problem here, of course, is that this formula does not
distinguish among 2005, 2006, 2007, etc.

So, maybe I've had too much to drink but I'm missing something here. How do
I get the first formula to distinguish among, say, August 2006 transactions
and August 2005 transactions? All I'm looking for is a count of a year/month
combo number of transactions expressed in a concatenated text string.

Dave

 

[Guest]

you messed up with the syntax

Here is the correct formula

=CONCATENATE(SUMPRODUCT(--(C2:C996="Aug"),--(YEAR(D2996)=2006))," 2006
August transactions")

 

[Guest]

Duh, I should have caught that one. Thinking too hard here.

Thanks.

Dave