0

Example:

Example]

How can I sum total G3:X3 in Y3 , but only sum if "paid" criteria is met. sum (feb,Q1,Apr,1H)

Improve this question
3

4 Answers 4

Reset to default
4

Use SUMIF:

=SUMIF(G3:W3,"Paid",H3:X3)

Note the same size but offset ranges.

Improve this answer
1
  • oh wow this works, thankyou for this you made my job easier
    – Hilman Maulana
    Jun 28, 2019 at 6:03
1

I prefer this solution:

enter image description here

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

Improve this answer
1
  • thankyou for your suggestions, i also prefer that solutions. but it needed to be in only 1 rows. thanks again though
    – Hilman Maulana
    Jun 28, 2019 at 6:05
1

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.

Improve this answer
2
  • Note: In excel, Offset is a volatile function. This is not the end of the world, it just means that the functions will recalculate anytime information changes on a worksheet/workbook even when it does not affect the formula. Regular formulas only recalculate when something that affects them changes. In small batches not so bad. When repeated over a large number of cells, it could get cumbersome to work with the sheet.
    – Forward Ed
    Jun 25, 2019 at 13:54
  • thankyou for your alternatives, ill use this if i have the chance . thanks
    – Hilman Maulana
    Jun 28, 2019 at 6:04
0

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.

Improve this answer
1
  • thankyou for your answer ill use it when i can, thanks
    – Hilman Maulana
    Jun 28, 2019 at 6:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .