CODEWITHSUNDEEP
excel
Maruthi
Maruthi

Reputation: 79

Excel CountIf Formula With OR

I was looking for a excel formula to do a task. Tried using Countif,Countifs. But with no luck. Any help is appreciated.
Task as below.

Type--------------Primary Color--------------Secondary Color
Car----------------Blue--------------------------Red
Bike--------------Black-------------------------White
Car---------------Blue--------------------------Blue

I need a formula which gives me a count of Cars having blue as their colour(Either Primary Or Secondary)

Upvotes: 0

Views: 188

Answers (3)

tc_NYC
tc_NYC

Reputation: 302

If you don't want to do an array formula you can just do 2 countifs formulas (which are easier for people to read than array formulas)

=COUNTIFS(b8:b12,"Blue")+COUNTIFS(c8:c12,"Blue")

Upvotes: 0

BrakNicku
BrakNicku

Reputation: 5991

You can use following array formula (confirmed with Ctrl+Shift+Enter to calculate count of blue cars:

=SUM(N((B2:B4="Blue")+(C2:C4="Blue")>0)*(A2:A4="Car"))

or non array version:

=SUMPRODUCT(N((B2:B4="Blue")+(C2:C4="Blue")>0)*(A2:A4="Car"))

This part:

(B2:B4="Blue")+(C2:C4="Blue")>0

is an alternative way of expressing OR (not suitable for array formulas as it always returns a single value). N function converts boolean values to 0 and 1.

Edit: updated the formulas to include condition for A column.

Upvotes: 2

Matt Cremeens
Matt Cremeens

Reputation: 5151

What about adding a column with the following

=IF(OR(B1="Blue", C1="Blue"), 1, 0)

and copy that down.

On another sheet you can sum that new entire column with 

=SUM(D:D)

Of course you will have a worksheet reference to the other sheet attached to the SUM formula.

Upvotes: 0

Related Questions