Example:
How can I sum total G3:X3 in Y3 , but only sum if "paid" criteria is met. sum (feb,Q1,Apr,1H)
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityExample:
How can I sum total G3:X3 in Y3 , but only sum if "paid" criteria is met. sum (feb,Q1,Apr,1H)
Use SUMIF:
=SUMIF(G3:W3,"Paid",H3:X3)
Note the same size but offset ranges.
I prefer this solution:
Where the Formula inside the Cell next to "Total" is in this case
=SUMIF(D2:D11;"Paid";E2:E11)
that way you can filter out "paid" or "bill" or sort by amount with ease
This formula will sum numbers in the given range that has "Paid" in the cell LEFT of them:
=SUM(IF(OFFSET(H3:M3;0;-1)="Paid";H3:M3;0))
... now, this is a "3D-formula" so edit/type in and press CTRL-SHIFT+ENTER at the end
(NOTE: ALT-CTRL-SHIFT+ENTER in LibreOffice)
If you press JUST ENTER the sum will be "0" regardless of "Paid" being present.
I typed in H3:M3 instead of H3:X3 as you have columns with Q1, Q2 and H1 as headers; assuming you do NOT wish to have these make the sum be double the actual total.
In a similar concept to Scott Craner's answer, you can offset the ranges. This will have the effect of lining G3 with H3 and so one down the row. Scott's answer used SUMIF
function. This solution will use the alternative of SUMPRODUCT
.
=SUMPRODUCT(--($G3:$W3="Paid"),$H3:$X3)
Note SUMPRODUCT performs array like calculations. Make sure your limit it to your data and do not use full column/row references.
The better formula in my opinion is SUMIF. This was simply a demonstration of an alternative.