Sum a range greater than >25000 based on the status of a check box

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

  • Paul NewcomePaul 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.

  • 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 NewcomePaul 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?

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you able to sum two of those numbers?

  • @Diana@Diana
    edited 01/27/20

    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 NewcomePaul 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.

  • thanks for all your help!

Sign In or Register to comment.