error in value excel error while using a formula

Question

I would like to find the number of dead black cats in the following table using excel formula.

Column B   Column C    Column D      Column E
(Animal)   (Color)      (DOB)      (DateOfDeath)
  Cat       Black     03/08/2010    03/08/2015
  Cat       White     03/08/2010
  Cat       Black     03/08/2010    03/08/2015
  Dog       White     03/08/2015    03/08/2015
  Dog       White     03/08/2015    03/08/2015
  Cow       Black     03/08/2015    
  Cat       Black     03/08/2015    
  Cat       White     03/08/2016

The formula which I used is:

=AGGREGATE(2, 6, (B3:B10="Cat")*(C3:C10="Black")*(NOT(ISBLANK(E3:E10))))

For some reason, it is not working, I don't know what mistake I have made.

Andreas N. · Accepted Answer · 2015-08-03 12:59:20Z

1

Since the argument (B3:B10="Cat")*(C3:C10="Black")*(NOT(ISBLANK(E3:E10))) is equivalent to an array of 0 and 1, you could just sum it.

{=SUM((B3:B10="Cat")*(C3:C10="Black")*(NOT(ISBLANK(E3:E10))))}

ctrl + shift + enter to evaluate SUM as an array formula (adding the {} brackets)

answered Aug 3, 2015 at 12:59

Andreas N.

3461 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Lusch · Accepted Answer · 2015-08-03 13:28:21Z

0

I have not worked with the Aggregate function yet, looking at it though it seems to not necessarily fit what you are trying to do. I would recommend to use the countifs function instead.

            =COUNTIFS($B$2:$B$9,AnimalType,$C$2:$C$9,AnimalColor,$E$2:$E$9,">0")

This function evaluates each criteria, in columns B, C, and D against AnimalType, AnimalColor, and whether the date is greater than zero. I duplicated the data set and entered the formula which generated the correct results.

answered Aug 3, 2015 at 13:28

Richard Lusch

1,19010 silver badges19 bronze badges

Comments

Chicago Excel User · Accepted Answer · 2015-08-03 16:13:36Z

0

Keep in mind that not all aggregate functions handle arrays (only function numbers 14 and above handle arrays).

2nd - you cannot mutply trues and falses. You need to conver them to number values first by adding -- in front.

If you would like to do this calculation using arrays I'd suggest using this

=SUMPRODUCT(--(B3:B10="Cat")*--(C3:C10="Black")*--(NOT(ISBLANK(E3:E10))))

Otherwise Use COUNTIFS as suggested by the above poster.

answered Aug 3, 2015 at 16:13

Chicago Excel User

2631 silver badge9 bronze badges

Comments

Manuel · Accepted Answer · 2015-08-06 04:35:23Z

0

You can derive it by Total Number of Black Cats - Total No of Black Cats with empty DateofDeath

=COUNTIFS(B2:B9,"Black",A2:A9,"Cat") - COUNTIFS(B2:B9,"Black",A2:A9,"Cat",D2:D9,"")

Alternative Direct Method would be =COUNTIFS(B:B,"Cat",C:C,"Black",E:E,"<>")

edited Aug 6, 2015 at 4:35

answered Aug 3, 2015 at 13:04

Manuel

2975 silver badges15 bronze badges

1 Comment

Manuel Over a year ago

@pnuts , You are correct. <> didnt strike my mind and thats more direct method

Collectives™ on Stack Overflow

error in value excel error while using a formula

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related