Count Multiple columns and criteria

Hi,

I know that this has been answered in some form or another, yet I still am having troubles.

What I'm trying to do count the TRUE values in multiple columns, if the criteria is correct in another column.

I've tried countifs but end up having the company included into the count, or only count the row that matches all the criteria. If I do =COUNTIFS(A2:A7,"A",B2:B7,"TRUE")+COUNTIF(C2:C7,"TRUE") then I get 5. When I change it to +COUNTIFS(A2:A7,"A",C2:C7,"TRUE") it works but there's a time where I need to check up to 8 Options.

I'd rather a easier way to do this if there is one please let me know.


Any help is welcomed.

Company	Option 1	Option 2
a	True	True
b	True	False
c	True	False
a	True	False
a	True	False
b	True	True

I should get 4 as there's 4 times True shows up for company "a". If I put all my criteria into one countifs statement then it will count the number of times company "a" appears and gives me 7.

Thanks that worked, I was having trouble with the sumproduct formula, couldn't quite understand how it work.

Why don't you add a column, = AND(A1 = "a",OR(Opt1,Opt2)) Then count TRUE values with COUNTIF.

John

Originally Posted by AZ-XL

Maybe this
Column A Names. Row1 Headers. Range B2:I7 with value(true/false)

=SUMPRODUCT(($A$2:$A$7="A")*$B$2:$I$7)

This works perfectly. Thanks for the help.

Two more questions for sumproduct, if I want to add another criteria to the array how would I do that. To use the example from the op if I wanted to include company c in the count then would it be:
=SUMPRODUCT(($A$2:$A$7={"A","C"})*$B$2:$I$7)
OR
=SUMPRODUCT(($A$2:$A$7="A")*$B$2:$I$7)+SUMPRODUCT(($A$2:$A$7="C")*$B$2:$I$7)

Or is there a better way to solve for this?