I was hoping someone could help me find a way to simplify this formula:
=IF(B5=0,"",SUMIFS($H$14:$H$456,$O$14:$O$456,B5,$S$14:$S$456,$A$1))+IF(B5=0,"",SUMIFS($H$14:$H$456,$P$14:$P$456,B5,$S$14:$S$456,$A$1))+IF(B5=0,"",SUMIFS($H$14:$H$456,$Q$14:$Q$456,B5,$S$14:$S$456,$A$1)+IF(B5=0,"",SUMIFS($H$14:$H$456,$R$14:$R$456,B5,$S$14:$S$456,$A$1)))
Thank you!
What result (if any) does this return?
Please Login or Register to view this content.
Spread the love, add to the Rep
"None of us are as smart as all of us."
First simplification, Though minor may prove helpful to future formula development.
Instead of
IF(B5=0,"",1stSumif))+IF(B5=0,"",2ndSumif))+etc..
Do
=IF(B5=0,"",1stSumif+2ndSumif+etc)
But for this task, I'd recommend a helper column, Say S
In S14 and filled down, put
=OR(O14=B$5,P14=B$5,Q14=B$5,R14=B$5)
Then use
=IF(B5=0,"",SUMIFS($H$14:$H$456,$S$14:$S$456,TRUE,$S$14:$S$456,$A$1))
No need for the double unary...Originally Posted by mcmahobt
=SUMPRODUCT(($O$14:$O$456=B5)+($P$14:$P$456=B5)+($Q$14:$Q$456=B5)+($R$14:$R$456=B5),$H$14:$H$456)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony,
Do we know the criteria range/criterion for the SUMIFS aren't text string matches? That was my reasoning for adding the double unary, but not sure if there was something implicit within the use of SUMIFS that gave away a numerical match?
The purpose of the -- is to convert True/False results into 1/0 results.
That function is already accomplished with the + between each criteria (criteria1)+(Criteria2)
Thanks for the explanation Jonmo!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks