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




Note the same size but offset ranges.

  • oh wow this works, thankyou for this you made my job easier – Hilman Maulana Jun 28 '19 at 6:03

I prefer this solution:

enter image description here

Where the Formula inside the Cell next to "Total" is in this case


that way you can filter out "paid" or "bill" or sort by amount with ease

  • 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 '19 at 6:05

This formula will sum numbers in the given range that has "Paid" in the cell LEFT of them:


... now, this is a "3D-formula" so edit/type in and press CTRL-SHIFT+ENTER at the end

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.

  • 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 '19 at 13:54
  • thankyou for your alternatives, ill use this if i have the chance . thanks – Hilman Maulana Jun 28 '19 at 6:04

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.


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.

  • thankyou for your answer ill use it when i can, thanks – Hilman Maulana Jun 28 '19 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.