CODEWITHSUNDEEP
functiongoogle-sheetssumifsgoogle-sheets-formula
vytfla
vytfla

Reputation: 559

Calling the contents of a cell inside a SUMIF function

I have a budgeting spreadsheet where I am trying to get the sum of cells in col A if the string in Col B is a certain keyword. For example, one of my formulas is =SUMIF(D13:D36,"Restaurant",C13:C36), so if any of cells in Col D are Restaurant, it takes the amount in Col C and adds them up. I have about 10 of the same formula, each with a unique string/keyword. This function works fine.

My problem is when I add a lot of entries and my columns get longer than the code specifies, I need to go into each formula separately and update the cells (eg, change C13:C36 to C13:C45, but 10 times).

If I enter "C13" into a cell, for this example T1, is it possible to call the contents in cell T1 within the SUMIF function? So the function would look something like =SUMIF(D13:D36,"Restaurant",CELL(T1):C36). I know the CELL function doesn't work here but is there something that could?

What I am trying to do is write the start and ending cells somewhere in my sheet then call them inside the SUMIF function, so if I need to change them later I only need to update 4 cells rather than 10+.

Upvotes: 0

Views: 188

Answers (2)

player0
player0

Reputation: 1

you could use simple QUERY like:

=QUERY({C13:D}, "select Col2, sum(Col1) 
                 where Col2 matches 'Restaurant' 
                 group by Col2 
                 label sum(Col1)''", 0)

or for the whole group:

=QUERY({C13:D}, "select Col2, sum(Col1) 
                 where Col2 is not null 
                 group by Col2 
                 label sum(Col1)''", 0)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

First as stated in the comments, there is no cost to using full columns:

=SUMIF(D:D,"Restaurant",C:C)

Which now it does not matter how large it gets.

But if one wants to limit it using other cells, I would use INDEX, instead of INDIRECT as INDIRECT is volatile(This only works in Excel):

=SUMIF(INDEX(D:D,T1):INDEX(D:D,T2),"Restaurant",INDEX(C:C,T1):INDEX(C:C,T2))

Where Cell T1 holds the start row and T2 holds the end row.

Upvotes: 2

Related Questions