Stump the Excel Expert

Matt Pinkston
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom FarrarConsultant

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..
Matt PinkstonEnterprise Architect

Author

Commented:
its a one time table that we need to use just wanted some formulas
SteveCost 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)

Open in new window

Becomes
=SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India")

Open in new window


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*")

Open in new window

OR
=SUMIFS(Employees!Q2:Q74, Employees!I2:I74 , B3 , Employees!D2:D74 , "India", Employees!S2:S74 , "active")

Open in new window

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")

Open in new window


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)

Open in new window


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*")

Open in new window


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*")

Open in new window

Matt PinkstonEnterprise Architect

Author

Commented:
Awesome help!!!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial