Countifs multiple criteria across a row

[theonlyhamish]

Hi all, Just a beginner and have tried various iterations of the countifs and sum functions to achieve this calculation.

I am trying to generate a count of 3 columns with mixed criteria so that i can feed the data simply into a fixed report page (i was using a pivot table but the boss doesnt like the way that 'looks')

Ive attached an image with made up data and what i thought was the right formula Columns A,C,D have different values and i need to count at the end of that row if the row meets all 3 of the variables. There is likely a more simplistic way of achieving this than i have found!

 

[theonlyhamish]

Though i should add in a typed version of my current formula

=SUM(COUNTIFS(A2,{"House","Bungalow","Boat"},C2,{"A";"B";"C"},D2,{"1","2","3"}))

 

[JV0710]

Hi

As an example . . . What is the answer you would like to see in cell H2 ?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(OR(A2={"House","Bungalow","Boat"}),OR(C2={"A","B","C"}),OR(D2={"1","2","3"}))

 

[Phuoc]

Maybe this:

=OR(A2={"House","Bungalow","Boat"})*OR(C2={"A","B","C"})*OR(D2={"1","2","3"})

 

[theonlyhamish]

Hi

As an example . . . What is the answer you would like to see in cell H2 ?

Should simply be a '1' to reflect that at least one of the variables from each column has been met.

so a bungalow with category A and score 2 (displays 1)
a bungalow with category A and score 5 (displays 0)

I have a table (for the boss) that simply displays how many times a person has inspected a building type each month but one of the columns has to display how many times they have visited (eg) a bungalow in categories a-c that also scores 1-3. If that makes sense

 

[theonlyhamish]

Hi & welcome to MrExcel.
How about

=SUM(OR(A2={"House","Bungalow","Boat"}),OR(C2={"A","B","C"}),OR(D2={"1","2","3"}))

Hi and thanks, i have tried this and i get a score '1' but some of the variables haven't been met, what im aiming for is '1' if at least one variable from each of the cells has been met.

so a bungalow with category A and score 2 (displays 1)
a bungalow with category A and score 5 (displays 0)

 

[Fluff]

Ok, how about

=--(SUM(OR(A2={"House","Bungalow","Boat"}),OR(C2={"A","B","C"}),OR(D2={"1","2","3"}))=3)

Or use the formula that Phuoc provided.

 

[JV0710]

For more clarity :
If column A is "House, Bungalow or Boat", then if the Category (column C ) is "A, B or C", the Score (column D ) must be 1,2 or 3 for a result of 1 else 0

Is that correct

 

[theonlyhamish]

For more clarity :
If column A is "House, Bungalow or Boat", then if the Category (column C ) is "A, B or C", the Score (column D ) must be 1,2 or 3 for a result of 1 else 0

Is that correct

YES