IF Checkbox flagged, SUM help requested

Coen

I am trying multiple variations on a Sum/IF formula and can't seem to crack it!

My goal is to SUM the rows in the [Estimated Refund Value] Column IF the [Credit Calculator] box is checked.

I've tried this in various forms including using the SUMIF feature as well as my current iteration below

=IF([Credit Calculator]:[Credit Calculator] = 1, SUM([Estimated Refund Value]:[Estimated Refund Value]), "")

not sure what I am missing, but I feel it's something small.

Paul McGuinness

Hi @Coen

This should do the trick

=SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator],TRUE)

Hope that helps

Thanks

Paul

Nick Korna

Hi @Coen,

This should do what you're after:

=SUMIF([Credit calculator]:[Credit calculator], true, [Estimated Refund]:[Estimated Refund])

Hope this helps; if you've any problems/questions then just post! 🙂

Monique Odom-Stearn

Hello @Coen,

Try this:

=SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator], 1)

Coen

Thank you, both!

Unfortunately I am still getting errors, this is leading me to think maybe it's not the formula but something else I've set up?

@Paul McGuinness : =SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator], true) - I get an Invalid Operation error. :(

@Nick Korna I also get an invalid operation error with your suggestion of =SUMIF([Credit Calculator]:[Credit Calculator], true, [Estimated Refund Value]:[Estimated Refund Value])

BUT - If I change my column I want to SUM to a column without a formula, this works.

=SUMIF([Credit Calculator]:[Credit Calculator], true, Cost:Cost) - This works

Could it be possible that I am getting errors because I have a column formula set up in the [Estimated Refund Value] Column?

I really appreciate the effort and suggestions :)

Coen

Thank you @Monique_Odom_Comcast I get an invalid operation error

BUT!

if I use a column that doesn's have a formula in it; it works.

=SUMIFS(Cost:Cost, [Credit Calculator]:[Credit Calculator], 1)

Cost is the column that I am using to calculate the [Estimated Refund Value] Column.

Monique Odom-Stearn

Hmmm... I don't think a formula will cause an issue, as long as the result is a number. The SUM functions only work on numbers.

@Coen, if you appropriately have numbers (manual or calculated by formula), can you send screenshots of your sheet (with any confidential information removed or blacked out)?

Coen

@Monique_Odom_Comcast Images below :)

Estimated Refund Value formula is =Cost@row * 0.85

The formula, using Cost and not [Estimated Refund Value] works.

If I add a calculation to the end of the formula I get positive results

=SUMIFS(Cost:Cost, [Credit Calculator]:[Credit Calculator], 1) * 0.85

Nick Korna

Do you have any errors in your Estimated Refund Value column? If there are any errors in there, these will stop the SUMIF from working properly.

Coen

@Nick Korna I did!

I've removed the error and am going to re-test the suggestions below! thank you!!

@Nick Korna @Monique_Odom_Comcast @Paul McGuinness

I had one row in my Estimated Refund Value column that had an error in it. I removed the error and all your suggestions worked! Thank you all!!