Complex conditional summing - array COUNT works, array SUM gives #VALUE
in [Functions]
|
From: fatcatfan on 17 Nov 2009 19:19 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
From: Pete_UK on 17 Nov 2009 21:02 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 On Nov 18, 12:19 am, fatcatfan <fatcat...(a)gmail.com> wrote: > 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
From: T. Valko on 17 Nov 2009 22:12 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? -- Biff Microsoft Excel MVP "fatcatfan" <fatcatfan(a)gmail.com> wrote in message news:ec55c830-6479-4ee1-b34d-f3dad8bfef50(a)p35g2000yqh.googlegroups.com... > 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
From: fatcatfan on 18 Nov 2009 09:52 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! On Nov 17, 10:12 pm, "T. Valko" <biffinp...(a)comcast.net> wrote: > 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? > > -- > Biff > Microsoft Excel MVP > > "fatcatfan" <fatcat...(a)gmail.com> wrote in message > > news:ec55c830-6479-4ee1-b34d-f3dad8bfef50(a)p35g2000yqh.googlegroups.com... > > > 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
From: T. Valko on 18 Nov 2009 13:41 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 "fatcatfan" <fatcatfan(a)gmail.com> wrote in message news:1a469527-8e2f-4d1b-82d5-6e15298f29df(a)m16g2000yqc.googlegroups.com... 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! On Nov 17, 10:12 pm, "T. Valko" <biffinp...(a)comcast.net> wrote: > 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? > > -- > Biff > Microsoft Excel MVP > > "fatcatfan" <fatcat...(a)gmail.com> wrote in message > > news:ec55c830-6479-4ee1-b34d-f3dad8bfef50(a)p35g2000yqh.googlegroups.com... > > > 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
|
Pages: 1 Prev: Copy specific data form sheets into master sheet Next: edit, fill, justify |