Trying to drag down a sum if formula but keeping the same range

Question

Trying to drag down a sum if formula but keeping the same range

Sarah Saleh 0

Hi,

I am using the below formular.

User's image

How can I make it so the "C1" drags down to C2, C3 etc without having to do it one by one??

Sorry if this is a silly question I just can't seem to manage it.

I have a lot of different codes to use so would take me a while!

I also need to figure out how to not repeat this process every month but the range will change every month. Would there be a quicker way of doing this at all please??

Many thanks,

Sarah

0 comments

4 answers

Your answer

Answer 1

Now that we know "C1" is a value and not a reference, we can solve that piece of the problem. You originally said the values run in sequence: C1, C2, etc. The Excel function CONCAT can build such a sequence in successive rows. You tell the function what character(s) form the prefix (for example C) and the number of the row above the first occurrence. For example, if your formula starts in row 12, your would call CONCAT("C",ROW()-11). In row 12, this function would evaluate to "C1". As you copy it down, it would evaluate to "C2", "C3", ... in successive rows. Your formula in row 12 would be


=SUMIF('Invoices 26-27'!$F$48:$F$75,CONCAT("C",ROW()-11),'Invoices 26-27'!$J$48:$J$75)

As you copy the formula down, the actual text of the formula remains unchanged. But when the formula is evaluated, the second argument which calls CONCAT will evaluate to the successive strings you want. Change the "11" to the row before your first formula and then copy it down as far as you need.

If your values do not run in sequence, there are other approaches that will let you enter the values only once and select the one you want to use in each of a sequence of formulas.

You also mentioned needing to handle monthly updated. We need more information. Nothing in your previous posts mentioned dates or data segregated by dates. Tell us what data in your workbook identifies what date applies to an entry.

It would be very helpful if you could provide a screen shot with a few rows of data. If the actual data is sensitive, realistic dummy values would work just as well.

Sarah Saleh 0 Reputation points

2026-11-06T19:59:30.34+00:00

Thankyou so much for your answer. I will send a screenshot on Monday if that’s ok. Just wanted to reply to say I’ve seen your reply and will respond on Monday.

Many thanks,

Sarah

Answer 2

Barry Schwarz 6,431

Now that we know "C1" is a value and not a cell reference, we can solve that part of the problem. You originally said the values are sequential, C1, C2, etc. There is an expression that will construct such a sequence as you copy the formula down a column. It depends on the row where you enter the first formula. For sake of an example, assume this formula is in row 12. Then the formula you want is


=SUMIF('Invoices 26-27'!$F$48:$F$75,CONCAT("C",ROW()-11),'Invoices 26-27'!$J$48:$J$75)

That CONCAT

0 comments

Answer 3

Igihozo Gady 80 Independent Advisor

Hello @Sarah,

Thank you for contacting Microsoft Community.

To keep your calculation ranges fixed while allowing the criteria cell to change as you drag the formula down, you can use:

=SUMIF('Invoices 26-27'!$F$48:$F$75,C1,'Invoices 26-27'!$J$48:$J$75)

The $ signs keep the ranges fixed, while C1 automatically changes to C2, C3, and so on when you drag the formula down.

Regarding your monthly updates, you could also consider using an Excel Table, which automatically expands when new rows are added, reducing the need to adjust your formula ranges manually.

To recommend the most suitable approach, could you please confirm whether you add new invoice rows to the same worksheet each month or create a separate worksheet for every month?

I hope this helps.

Best regards.

Sarah Saleh 0 Reputation points

2026-11-05T10:39:04.1933333+00:00

Hi,

Many thanks for your response. New invoices are all added to the same worksheet.

The table sounds good however I never used a table within excel so wouldn't know how to create one.

Is there a way to use SUMIF using a date also? Like any rows with an October dates adds up the codes?

Sorry I am not explaining this very well. I have been tasked with creating a budget spreadsheet. I have got so far but now I'm a bit stuck hence my original question!

Many thanks,

Sarah
Igihozo Gady 80 Reputation points Independent Advisor

2026-11-05T18:04:53.65+00:00

Hello @Sarah,

Thank you for the update. You've explained what you're trying to achieve clearly, so no worries!

Since you're adding new invoices to the same worksheet, an Excel Table should make things much easier.

Step 1: Create an Excel Table

Open your Invoices 26-27 worksheet.

Click anywhere inside your invoice data.

Press Ctrl + T on your keyboard.

Make sure the correct range is selected and check My table has headers.

Click OK.

Under Table Design, rename the table to Invoices.

The table should now automatically expand as you add new invoice rows.

You can also follow Microsoft's guide: Create and format tables in Excel.

Step 2: Calculate totals by code and month

Yes, you can do this using SUMIFS, which allows you to apply multiple conditions, such as an invoice code and a date range.

For example, assuming your table has columns named Code, Date, and Amount, and cell D1 contains 01/10/2026 (October 1, 2026), use:

=SUMIFS(Invoices[Amount],Invoices[Code],C1,Invoices[Date],">="&$D$1,Invoices[Date],"<"&EDATE($D$1,1))

This adds the amounts for the code in C1 where the invoice dates fall within October 2026.

For another month, simply change the date in D1 to the first day of that month.

You can also refer to Microsoft's guide: Sum values based on multiple conditions.

I hope this helps make your budget spreadsheet easier to manage!

Kind regards.
Sarah Saleh 0 Reputation points

2026-11-06T15:37:08.69+00:00

Hello,

Thankyou so much you are helping me a lot!!

Can I tell you which data is in each column if possible please.

Date - Column A

Code - Column F

Amount - Column J

I don't quite understand "For another month, simply change the date in D1 to the first day of that month" As each invoice is in a different row going down.

Also when I mentioned C1 before that is actually one of my budget codes not the cell. Sorry for not making that clear.

Is it worth noting that the worksheet where I am trying to put the formula is on a different worksheet than the invoices table.

So sorry about this!

Answer 4

Use a relative reference for the criteria cell instead of typing "C1" as text.

Change the formula to this pattern:

=SUMIF('Invoices 26-27'!$F$48:$F$75,C1,'Invoices 26-27'!$J$48:$J$75)

Then drag down. Excel will change:

C1 to C2
C2 to C3
and so on

The ranges stay the same because of the $ signs:

$F$48:$F$75
$J$48:$J$75

If C1 must always stay in column C while dragging down, this version is safer:

=SUMIF('Invoices 26-27'!$F$48:$F$75,$C1,'Invoices 26-27'!$J$48:$J$75)

That locks the column to C but still lets the row change as the formula is filled downward.

For future months, the quickest approach is usually to set the formula once with the correct absolute ranges and then use the fill handle to copy it down. Excel automatically adjusts relative references when formulas are copied.