How do I write my SUMIF formula so that it automatically sums numbers in its own column?

Options
Alex Kolker
Alex Kolker ✭✭✭
edited 11/23/22 in Formulas and Functions

All --

I am putting together a SUMIF function for Column Beta that adds together only those column entries whose Column Alpha have a specific value. This gets me:

=SUMIF(Alpha5:Alpha56,"Value",Beta5:Beta56)

According to the Help & Learning article on SUMIF, the "Beta5:Beta56" part of the formula is called the "sum range."

The question is: DO I ALWAYS HAVE TO NAME THE COLUMN FOR THE SUM RANGE? Is there any way to let the formula know I'm talking about summing the range in the same column the SUMIF function appears in? (For example, is there an "@column" function analogous to the "@row" function?)

I have to create this formula for hundreds of columns across different sheets and would hate to have to type in each column name manually (and twice).

Thanks in advance.

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/30/22 Answer ✓
    Options

    Hi @Alex Kolker

    There currently isn't a way to reference a column with something like "@column" in order to reference itself and make that dynamic. You would indeed need to type out the range again for each new column. Please feel free to Vote on this Enhancement Idea post, where another member has a similar request!

    That said, I agree that dragging your formula into the other columns is the easiest way to go.

    You can prevent the Alpha column from changing by using Absolute References:

    =SUMIF($Alpha5:$Alpha56,"Value",Beta5:Beta56)

    See: More Detailed Description of Reference Types

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!