Countifs Question

[TMILJVIPM]

Guys,

I need help with a countifs formula. In column G I need to recognize multiple items and on column J only if they are "onsite"



Here is the formula im using now:
=COUNTIFS('Opp ID - 50200'!$G$4:$G$180,"pipefitter",'Opp ID - 50200'!$J$4:$J$180,"Onsite")

But I need it to rcognize multiple crafts in column G. ex. pipefitter, mechanical A, Mechanical B, Mechanical Lead

I hope I explained this good enough.

Thanks.

 

[circledchicken]

Hi,

Try something like:

=SUM(
   COUNTIFS(
      'Opp ID - 50200'!$G$4:$G$180,
      {"pipefitter","mechanical A","Mechanical B","Mechanical Lead"},
      'Opp ID - 50200'!$J$4:$J$180,
      "Onsite"))

If you want to learn more about handling multiple criteria in conditional tests, here are some good related articles:

• http://www.xldynamic.com/source/xld.SUMPRODUCT.html
• SUMIF Formulas | RAD Excel

 

[T. Valko]

Guys,

I need help with a countifs formula. In column G I need to recognize multiple items and on column J only if they are "onsite"



Here is the formula im using now:
=COUNTIFS('Opp ID - 50200'!$G$4:$G$180,"pipefitter",'Opp ID - 50200'!$J$4:$J$180,"Onsite")

But I need it to rcognize multiple crafts in column G. ex. pipefitter, mechanical A, Mechanical B, Mechanical Lead

I hope I explained this good enough.

Thanks.

Use cells to hold the criteria:

A1 = pipefitter
A2 = mechanical A
A3 = mechanical B
A4 = Mechanical Lead
B1 = Onsite

Then:

=SUMPRODUCT(COUNTIFS('Opp ID - 50200'!$G$4:$G$180,A1:A4,'Opp ID - 50200'!$J$4:$J$180,B1))