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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!