Using SUMIF with a checkbox in Excel Online

Question

Does anyone know how I can get this formula to work in Excel Online?

I'm trying to create a formula that totals the amount invoiced and paid for, using the checkbox to determine the total.

I've tried using 'TRUE' and 'CHECKED' but neither seems to work in Excel Online. I know in Google Sheets that 'TRUE' works and the only documentation I can find for Excel references 'Format Control' - but I can't find that in Excel online.

Answer 1

Not sure why Snow suggests that this is a bug. You have simply mixed up the arguments. SUMIF takes the criteria_range first, then the criteria, then the sum_range. And then I recommend that you keep the dimensions of both the criteria_ range and the sum_range the same.

change your formula to:

=SUMIF(H8:H13, TRUE, B8:B13)

and it shall work.

Answer 2

When a checkbox is checked, the value of the cell is TRUE, not CHECKED.

2.

The rownumbers of the ranges in 1 formula must be the same;

you used B8:B14 and H8:H13,

it must be: B8:B13 and H8:H13

or B8:B14 and H8:H14

3. The solution of 'Snow Lu MSFT' is correct, but you also can use: =SUMIFS(B8:B14,H8:H14,TRUE)