# Excel help, Sum Row with criteria

Example:

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.

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

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

• 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:

`=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.

• 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`.

``````=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.

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