# Stump the Excel Expert

Matt Pinkston used Ask the Experts™
on
One more stumper Excel question, I am using these formulas

=SUMPRODUCT((Employees!I2:I74=B3)*(Employees!D2:D74="India")*Employees!Q2:Q74)
=COUNTIFS(Employees!I:I,B3,Employees!D:D,"India")

How would I
1. exclude entries where employees column S = mid wfr
2. exclude entries where employees column S CONTAINS "wfr"
3. include rows where employees column S = "active" or = "mid hire"
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consultant

Commented:
Been watching your questions, and thought if you data is formatted as a table on the Data tab, you may want to consider using a pivot table to accomplish all your filtering. Just a thought..
Enterprise Architect

Commented:
its a one time table that we need to use just wanted some formulas
Cost Accountant
Top Expert 2012

Commented:
Firstly SUMPRODUCT is an older, and slower, formula than SUMIFS which in this case may be better and more understandable:

Such that
``````=SUMPRODUCT((Employees!I2:I74=B3)*(Employees!D2:D74="India")*Employees!Q2:Q74)
``````
Becomes
``````=SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India")
``````

And then you can add to that such that:
``````=SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India", Employees!S2:S74 , "<>*wfr*")
``````
OR
``````=SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India", Employees!S2:S74 , "active")
``````
OR
``````=SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India", Employees!S2:S74 , "active")
+ SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India", Employees!S2:S74 , "mid hire")
``````

The SUMIFS is certainly more undertstandable than SUMPRODUCT
The  SUMPRODCUT would be:
``````=SUMPRODUCT((Employees!I2:I74=B3)*(Employees!D2:D74="India")*(EmployeesC2:C74="<>*wfr*")*Employees!Q2:Q74)
``````

These are not tested, but should be about right :)
Cost Accountant
Top Expert 2012
Commented:
COUNTIFS is the same as the SUMIFS just drop the range being summed (Employees!Q2:Q74) and the first comma:

``````=COUNTIFS(Employees!I2:I74 , B3 , Employees!D2:D74 , "India", Employees!S2:S74 , "<>*wfr*")
``````

I would also press F4 on the ranges to put dollar signs round it to 'fix' the ranges:

``````=COUNTIFS(Employees!\$I\$2:\$I\$74 , \$B3 , Employees!\$D\$2:\$D\$74 , "India", Employees!\$S\$2:\$S\$74 , "<>*wfr*")
``````
Enterprise Architect

Commented:
Awesome help!!!!!!

Do more with