Summation with a Condition

[GeorgeF]

I am summing monthly Invoice Amounts from an annual list of occurrence
using an array formula: SUM((MONTH(Range 1)=MONTH(A1)*(INVOICE for sam
Range 1)). It works perfectly for every month except January where th
amount is some very large unexplained number. The MONTH (Range 1) i
mmm-dd-yy and the MONTH(A1) is mmm. What is wrong

 

[Bob Phillips]

That is probably due to blank cells, which will evaluate to Jan.

Try something like

=SUM((Range 1<>"")*(MONTH(Range 1)=MONTH(A1))*(INVOICE for same Range 1))

 

[GeorgeF]

Hi Bob Phillips
Thanks a lot. Great answer. You are right, in that the Januar
collection of Invoice totals was blank cell sensitive. To test th
idea, I shortened the "Range 1" size to end of current data and got th
correct January Total, without your adddition. But, I must have a
extended range of blank cells for incoming future invoices that I wan
totalled automatically. Your prefix insert test for blank cells ha
yielded the correct amount for January. I carried the new formul
forward to all the months and altered the open cell length of the Rang
several times which still gave the correct amounts. Is it to
theoretical question to ask *why*? That is, checking for blank cell
seems to be a problem of January alone. It's not the cell locatio
because I substituted February and other months in the January cel
location and got the correct answers for the given mionth. Hmmmmm!!
Thanks again. George

 

[Bob Phillips]

Hi George,

It's all to do with dates. Excel stores dates a the number of days since,
and including, 1st Jan 19000. So a cell with 1st Jan 1900 in it actually
contains the value 1. You can actually enter 0/1/1900 (UK style that is,
0thJan 1900) in a cell, which equates to a value of 0, and this is the
reason.

If you have a blank cell, when you run a formula that would return a numeric
result, Excel treats it as 0. For instance, if A1 is blank, =2^A1 returns 1
(2^0). Similarly =MONTH(A1) returns 1, because it is effectively saying
=MONTH(0th Jan 1900). So in your formula, testing for Jan, all the blank
cells match, For Feb or any other month they do not. Eliminating the blanks
with a separate test eliminates the problem.

Regards

Bob