Complex conditional summing - array COUNT works, array SUM gives#VALUE

[fatcatfan]

I'm trying to use an array formula SUM with nested IFs to accomplish
some complex conditional sums. If I use "COUNT" as the outermost
function I get a number that appears to be correctly counting the
target cells. I've added a nested "IF(ISNUMBER(range))" to the count
function and verified that it returns the same number as the function
without it, so it would seem all the selected cells are, in fact,
numbers. I've even calculated a separate array formula of =AND(ISNUMBER
(range)) which returns TRUE, again to confirm all the cells in the
range contain numbers. However, as soon as I change the formula from
"COUNT" to "SUM" I get a #VALUE error. Any clue why?

The formula:
{=COUNT(IF(INT(VALUE(LEFT('1997'!$A$2:$A$1033,19))-5/24)=DATE(C
$2,$A3,$B3),IF(VALUE(LEFT('1997'!$C$1:$EC$1,2))=$A$1,IF('1997'!$C$2:$EC
$1033>0,'1997'!$C$2:$EC$1033))))}

this returns "9" but changing COUNT to SUM returns #VALUE

 

[Pete_UK]

You would normally have an arrangement like this:

=SUM(IF(condition_array,sum_array))

Your condition_array is quite complex, but you are not passing
anything to the sum_array, so nothing will be summed.

Hope this helps.

Pete

 

[T. Valko]

When I replace COUNT with SUM the *basic* formula works OK for me.

Hard to say what the problem is without seeing the data. Here's how I tested
it...

A1 = x

A2:A20 = random dates/times and some random letters at the end (making them
TEXT strings):

10/16/2009 12:25 AM xx
11/16/2009 06:11 PM xy
11/20/2009 01:46 PM aa

C1:E1 = x, y, x

C2:E20 = random numbers including empty cells, 0s and negative numbers

I used this array formula and got the correct result:

=SUM(IF(INT(LEFT(A2:A20,19)-5/24)=DATE(2009,11,16),IF(C1:E1=A1,IF(C2:E20>0,C2:E20))))

Are there already any #VALUE! errors in any of the ranges?

 

[fatcatfan]

The header row (C1:EC1 in my formula) contains data such as:
51-132 51-133 51-134 52-129 52-131 53-121 53-122 53-123 53-124
and for a given sum I want to match columns "51-*" or "52-*", etc.

The data/time column A is much as you'd guessed, text strings, and the
goal is to sum all the hourly values for an entire day after adjusting
for time zone.

The data (C2:EC1033) is -9999(bad/no data), zero, or a positive real
number.
=AND(ISNUMBER(C2:EC1033)) entered as an array formula returns TRUE.

Alternately, if I change the array formula to
=COUNT(IF(INT(VALUE(LEFT($A$2:$A$1033,19))-5/24)=DATE(1997,2,3),IF
(VALUE(LEFT($C$1:$EC$1,2))=51,IF(ISNUMBER($C$2:$EC$1033),$C$2:$EC
$1033))))
it returns "216" which is correct because there are 9 columns each
with 24 hours of matching data (9*24=216). I've since done a search
and replace to change the -9999s to zeroes, so I would expect that
changing the COUNT in this formula to SUM *should* give me the number
I'm looking for. Instead it continues to give #VALUE!, which is
perplexing because the formula itself should assure that whatever is
passed to SUM is a number.

Deadlines being what they are, I had to use other less elegant methods
to reach my answers, so my interest now is purely academic (and for
future reference).

Excel version is 2003 (11.8117.8122) SP2 if that makes any difference.

Thanks for your help!

 

[T. Valko]

Here's a small sample file that I setup based on the additional info you
provided.

xfatcatfan.xls 18kb

http://cjoint.com/?lstMW7aCDM

--
Biff
Microsoft Excel MVP


The header row (C1:EC1 in my formula) contains data such as:
51-132 51-133 51-134 52-129 52-131 53-121 53-122 53-123 53-124
and for a given sum I want to match columns "51-*" or "52-*", etc.

The data/time column A is much as you'd guessed, text strings, and the
goal is to sum all the hourly values for an entire day after adjusting
for time zone.

The data (C2:EC1033) is -9999(bad/no data), zero, or a positive real
number.
=AND(ISNUMBER(C2:EC1033)) entered as an array formula returns TRUE.

Alternately, if I change the array formula to
=COUNT(IF(INT(VALUE(LEFT($A$2:$A$1033,19))-5/24)=DATE(1997,2,3),IF
(VALUE(LEFT($C$1:$EC$1,2))=51,IF(ISNUMBER($C$2:$EC$1033),$C$2:$EC
$1033))))
it returns "216" which is correct because there are 9 columns each
with 24 hours of matching data (9*24=216). I've since done a search
and replace to change the -9999s to zeroes, so I would expect that
changing the COUNT in this formula to SUM *should* give me the number
I'm looking for. Instead it continues to give #VALUE!, which is
perplexing because the formula itself should assure that whatever is
passed to SUM is a number.

Deadlines being what they are, I had to use other less elegant methods
to reach my answers, so my interest now is purely academic (and for
future reference).

Excel version is 2003 (11.8117.8122) SP2 if that makes any difference.

Thanks for your help!