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!

 

[JV0710]

Here's my stab at it . . . I am sure there are more elegant ways though

=IF(OR(OR(A2="House"),(A2="Bungalow"),(A2="Boat")),IF(OR(OR(C2="A"),(C2="B"),(C2="C")),IF(D2<4,1,0),0))

 

[JV0710]

Apologies - I think that last 0 is in the wrong place . . . Here's the corrected formula

=IF(OR(OR(A6="House"),(A6="Bungalow"),(A6="Boat")),IF(OR(OR(C6="A"),(C6="B"),(C6="C")),IF(D6<4,1,0)),0)

 

[Fluff]

Judging by the image in the op & the formula being used, the values in col D are text & not numbers.

 

[theonlyhamish]

=IF(OR(OR(A6="House"),(A6="Bungalow"),(A6="Boat")),IF(OR(OR(C6="A"),(C6="B"),(C6="C")),IF(D6<4,1,0)),0)

This has pretty much nailed it!! One tiny thing is that some negative results are displaying '0' and some are displaying 'FALSE'. Any ideas?

 

[Fluff]

Did you try the formula in post#5 or post#8?

 

[theonlyhamish]

Judging by the image in the op & the formula being used, the values in col D are text & not numbers.

They were but ive since changed that to numbers

 

[theonlyhamish]

Did you try the formula in post#5 or post#8?

Yes but i was only getting zero results, i have only changed the text to numbers following your last post, would that be the cause.

I really need to spend more time on excel

 

[Fluff]

Yes, text & numbers are totally different, try

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

 

[Alex Blakenburg]

If you add Trim to @Phuoc's code it will work either way:

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

 

[theonlyhamish]

Yes, text & numbers are totally different, try

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

That is perfect and has fixed the small issue that JV0710's solution had.

Thank you all for your help!