Hi Ozgrid Member,
I built up this formula to calculate multiple criteria cells:
=IFERROR((U230/(SUMIFS($R$22:$DP$22,$R$15:$DP$15,U$225&"*",$R$15:$DP$15,V$225&"*",$R$15:$DP$15,W$225&"*"))), "N/A")asterisks represent T1, T2, and T3 to sum the data where T1W1, T1W2, T2W1, T2W2, T3W1... etc were seen. My main goal is to divide A (U230) to B (SUM parts) after SUM process.
It does not do SUM operation and nothing calculated. What is wrong with it?
Thank you in advance.
Re: Multiple Criteria SUMIFS does not work
Your sum criteria will be using AND type logic, so it's looking for a cell in R15:DP15 where value is T1* and T2* and T3*. Since such a thing can't exist, nothing meets criteria; aka sum is 0. Instead, could be:
=IFERROR(U230/(SUMIFS($R$22:$DP$22,$R$15:$DP$15,U$225&"*") + SUMIFS($R$22:$DP$22,$R$15:$DP$15,V$225&"*") + SUMIFS($R$22:$DP$22,$R$15:$DP$15,W$225&"*")), "N/A")
By calculating each one separately, it's treated like an OR type logic, and you should get some results.
Re: Multiple Criteria SUMIF Error
Another way to write that would be
=IFERROR(U230/SUMPRODUCT((SUMIFS($R$22:$DP$22, $R$15:$DP$15, U$225:W$255&"*")), "N/A")
Re: Multiple Criteria SUMIFS does not work
Hello Luke M, thank you very much it works like a charm!
Re: Multiple Criteria SUMIF Error
Quote from mikerickson;776914Another way to write that would be
=IFERROR(U230/SUMPRODUCT((SUMIFS($R$22:$DP$22, $R$15:$DP$15, U$225:W$255&"*")), "N/A")
Hello Mr. Rickson,
Actually, i tried your formula because i thought it could be more useful considering it's shorter than Ozgrid's Luke M, however, i got an error; maybe you missed a part or parenthesis?
Re: Multiple Criteria SUMIF Error
If your numbers are in row 22 and the text you are "if'ing" against are in row 15 and the test values are in row 255, that should work.
Don’t have an account yet? Register yourself now and be a part of our community!