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

2 answers

Your answer

Answer 1

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.

Answer 2

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.