Sumproduct not returning correctly

[Nalani]

Can somone tell me why the Sumproduct formulas below are reading the blank cells as JAN.

Excel Workbook
	G	H	I	J
1		Bill 1	Bill 2	Bill 3
2	JAN	$6,147.00	$15,331.50	$25,069.50
3
4	$6,343.00	-$203.00	12/21/10	1/3/11
5	$6,343.00	$397.00	12/31/10	1/8/11
6	$6,255.00	$2,325.00	1/8/11	1/21/11
7	$6,109.00	$1,478.00	1/14/11	1/26/11
8	$5,519.00	$437.00	1/5/11	1/14/11
9	$6,607.00	$678.00	1/21/11	1/28/11
10	$6,173.00	$17.00
11	$3,967.00	-$2,970.00	3/9/11	3/9/11
12	$617.00	$803.00	3/9/11
13	$6,109.00	$105.00	2/25/11	3/10/11
14	$6,173.00	$215.00	2/23/11
15	$6,173.00	$215.00	3/11/11	3/16/11
Sheet2

The correct answers should be:

Excel Workbook
	H	I	J
18	Bill 1	Bill 2	Bill 3
19	$5,112.00	$12,245.00	$18,588.00
Sheet2

Thanks

 

[barry houdini]

In some cases Excel treats a blank cell as zero....and in Excel "date zero" is "0th" of Jan 1900, so if you use, e.g.

=MONTH(A1)

where A1 is blank you get 1 (for January)...it's also a Saturday (excel says) so

=WEEKDAY(A1)=7 (assuming 1900 date system)

so in cases like this you need to add a criteria for the year perhaps...or check the cells aren't blank like this:

=SUMPRODUCT((TEXT($J$3:$J$67,"mmm")=$G$2)*($J$3:$J$67<>""),$H$3:$H$67)

 

[Nalani]

Thanks Barry

Your explanation taught me something new today.

Your formula works by checking for blanks.

How would I put in a criterior for the year because I can see that further down in my table I will have Dec 2011.

In this snippet of my table you can see that I already have Dec 2010 so I wouldn't want to add 2010 and 2011 numbers together.

 

[barry houdini]

If you make G2 Jan-11 then try using this version

=SUMPRODUCT((TEXT($J$3:$J$67,"mmm-yy")=TEXT($G$2,"mmm-yy"))+0,$H$3:$H$67)

 

[Nalani]

Thanks again Barry

I have everything working correctly now.

Have a Great Day