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.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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.

@Diana

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.

Paul Newcome

@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?

@Diana

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.

Paul Newcome

What about the numbers? How are those input?

@Diana

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.

Paul Newcome

Are you able to sum two of those numbers?

@Diana

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.

Paul Newcome

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.

@Diana

thanks for all your help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.