home / blog

Excel, counting multiple conditions

Say you wanted to count how many red horses there are in this data set.

Animal Color
Horse	Red
Cat	Yellow
Frog	Green
Frog	Orange
Cat	Yellow
Horse	Red
Rabbit	Blue
Horse	Pink

COUNTIF sounds likely as it can be used to count occurrences of items in a given range, e.g. =COUNTIF(A2:B9, "=Horse") It cannot however be used to evaluate multiple conditions.

Another approach is to use array formulas. Array formulas require magic key-strokes (Ctrl-Shift-Enter) or non-standard syntax, i.e. {} in excel and different things in open office / google apps.

I prefer this solution using the lesser-known SUMPRODUCT() and –() functions. SUMPRODUCT counts numerical rows with matching values. The –() function converts Boolean true and false to 1 and 0 respectively, this allows SUMPRODUCT to do the counting.

=SUMPRODUCT(--(A2:A9="Horse"),--(B2:B9="Red"))
This entry was posted in geek and tagged . Bookmark the permalink.

One Response to Excel, counting multiple conditions

  1. Simon Platten says:

    Hi adam, I saw you Lego printer on you tube and was very impressed with it. I am a professional software engineer, not to great with electronics. I have a project which I would appreciate your advice on. I would like to use a photo reflector SY-CR102 to read barcodes or something similar. I have an arduino UNO 3 which I would like to use. The idea is to use it to read a code from the underside of a slot car as it passes over the sensor. Do you think it could work?

Leave a Reply

Your email address will not be published.