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

Alex Kolker

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.

Genevieve P.

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

Leibel S

@Alex Kolker

If you copy and paste the cell into an adjacent formula it will automatically change the formula to the new column

Alex Kolker

@Leibel Shuchat

It would update the "Beta" references but not the "Alpha" ones?

Alex Kolker

I have tried this and both the Alpha and Beta references update this way. Is there maybe a way to avoid that?

Genevieve P.

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