Sum a range greater than >25000 based on the status of a check box
Help! I cannot figure out a formula for SUMIF or SUMIFS to add a range of numbers in a column that are greater than 25,000 based on a checkbox.
=SUMIFS({Content Limit  NB}, >25000, {Reporting  NB}, 1) < why doesn't this work. hehe
I keep getting #invalid operation. This formula works =SUMIF({Content Limit  NB}, >25000), but totals all the values. I need to narrow it down to the check box.
Please help.
Answers

Try adding in some @cell references.
=SUMIFS({Content Limit  NB}, @cell > 25000, {Reporting  NB}, @cell = 1)
If this does not work, check your {Reporting  NB} range to see if there are any errors. If there are errors in that range, the error will pull through your SUMIFS.

Hi Paul,
Sorry I forgot to mention that my formula is referencing another sheet, @cell only seems to work when the data is present on the same sheet. The {Reporting  NB} range is a check box column, it's only counting based on the checked rows, so i don't think there are any errors in the column.
Unfortunately this didn't work.

@cell does work for cross sheet references. I use it almost daily.
What about your other range? Are there any errors in there? What is used to generate the numbers (manual entry or formula)?
How are the boxes being checked?

Thanks Paul, I don't use @cell that much and yes you are correct, the other sheet uses a "vlookup" function to bring in the numbers from an application sheet. There could be errors there.
example: =VLOOKUP($[Cert #]19, {Sheet1}, 64, false)
I did change the sheet I was obtaining my data from to the source, that contains no formulas and I have reached yet another road block. The formula is still reporting an #invalid operation.
The checkboxes are just checkboxes with no formulas as well.

What about the numbers? How are those input?

The numbers are straight input. They have been autofilled using a form plugin, but still just straight numbers.
I have been able to retrieve the numbers I am looking for by using the filters, but still not achieving the auto calculations for my total reporting sheet.

Are you able to sum two of those numbers?

Yes, I can sum the whole column, and I can sum all numbers above 25,000, when I add the checkbox row, that's where the formula stops calculating.
=SUMIF({Content Limit  NB}, <25000) < this works.

Then you will want to check your checkbox column thoroughly to ensure there are no errors within that range. Otherwise the formula should be working.

thanks for all your help!
Help Article Resources
Categories
Check out the Formula Handbook template!