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

--
George
-----------------------------------------------------------------------
GeorgeF's Profile: http://www.excelforum.com/member.php...fo&userid=2412
View this thread: http://www.excelforum.com/showthread.php?threadid=37943

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))

--
HTH

Bob Phillips

"GeorgeF" wrote in
message ...

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


--
GeorgeF
------------------------------------------------------------------------
GeorgeF's Profile:
http://www.excelforum.com/member.php...o&userid=24124
View this thread: http://www.excelforum.com/showthread...hreadid=379436

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

--
George
-----------------------------------------------------------------------
GeorgeF's Profile: http://www.excelforum.com/member.php...fo&userid=2412
View this thread: http://www.excelforum.com/showthread.php?threadid=37943

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

"GeorgeF" wrote in
message ...

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


--
GeorgeF
------------------------------------------------------------------------
GeorgeF's Profile:
http://www.excelforum.com/member.php...o&userid=24124
View this thread: http://www.excelforum.com/showthread...hreadid=379436