Matt Pinkston
asked on
Stump the Excel Expert
One more stumper Excel question, I am using these formulas
=SUMPRODUCT((Employees!I2:
=COUNTIFS(Employees!I:I,B3
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"
=SUMPRODUCT((Employees!I2:
=COUNTIFS(Employees!I:I,B3
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"
Tom Farrar
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..
ASKER
its a one time table that we need to use just wanted some formulas
Firstly SUMPRODUCT is an older, and slower, formula than SUMIFS which in this case may be better and more understandable:
Such that
And then you can add to that such that:
The SUMIFS is certainly more undertstandable than SUMPRODUCT
The SUMPRODCUT would be:
These are not tested, but should be about right :)
Such that
=SUMPRODUCT((Employees!I2:I74=B3)*(Employees!D2:D74="India")*Employees!Q2:Q74)=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*")=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 , "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 :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome help!!!!!!