SumProduct - checking for multiple options in one column

[edeaston]

Hi,

I am trying to create a formula which will tell me how many projects I have
which meet certain criteria and I am having trouble setting up my formula to
look for multiple options in one column.

At the moment I have
SUMPRODUCT(--($A2$A100={"Criteria 1a","Criteria 1b"}),--($B2$B100="Criteria
2"),--($C2$C100="Criteria 3"))
but it doesnt work!

Could one of you good people help me out!?

Thanks

Ed

 

[Elkar]

Try it like this:

=SUMPRODUCT(--(($A2:$A100="Criteria 1a")+($A2:$A100="Criteria
1b")),--($B2:$B100="Criteria 2"),--($C2:$C100="Criteria 3"))

HTH
Elkar

 

[edeaston]

Works perfectly - thanks

Ed

Try it like this:

=SUMPRODUCT(--(($A2:$A100="Criteria 1a")+($A2:$A100="Criteria
1b")),--($B2:$B100="Criteria 2"),--($C2:$C100="Criteria 3"))

HTH
Elkar