How can I drag a formula and have the sheet numbers go up while also using SUMIF.

**EissejYaw** · 04-22-2012, 01:05 AM

I have a spreadsheet that has a sheet for each day and with a dropdown list of items sold for the day (D23:D54), with the prices appearing next to the item (I23:I54). On a seperate summary sheet, I have a SUMIF to add up a the value of each type of item sold that day.
I.e. =SUMIF(Sheet1!$D$23:$D$54,"=Other Service", Sheet1!$I$23:$I$54)
Column C is for sheet1, D for sheet 2 and so on... (until sheet 100)

I want to be able to quickly fill out the formulae up to sheet 100 for all the items, without having to manually change the number after 'sheet' as there are lots of items and 100 sheets and this would just take forever.

What is the best way to do this? Please help, I'm going mad trying to find the answer!

**dilipandey** · 04-22-2012, 01:12 AM

Hi EissejYaw,

Welcome to the forum.

without having to manually change the number after 'sheet'

To achieve this, you can use Indirect Function... do little research on this function over Google or Excel Inbuilt Help (F1)

Regards,
DILIPandey

<click on below 'star' if this helps>

**EissejYaw** · 04-22-2012, 01:23 AM

Thanks Dilipandey. I have tried to look into indirect functions but can't find/work out how to fit it in with my particular situation. Can anyone help to fit it in with the formula I am already using?

**Haseeb Avarakkan** · 04-22-2012, 01:56 AM

Hello,

On summary column C you need SUMIF from Sheet1, column D sumif from Sheet2, column E sumif from sheet3 etc...
If this you requirement try this in column C, then copy across.

=SUMIF(INDIRECT("Sheet"&COLUMNS($C:C)&"!D23:D54"),"=Other Service", INDIRECT("Sheet"&COLUMNS($C:C)&"!I23:I54"))

**EissejYaw** · 04-22-2012, 02:11 AM

Sorry!!!! My silly mistake, I've been up all night. It works! Thank you!!! You're a hero

**EissejYaw** · 04-22-2012, 02:10 AM

Haseeb - Excel accepts the formula but the cells just come up blank.

**Haseeb Avarakkan** · 04-22-2012, 02:15 AM

...deleted...

How can I drag a formula and have the sheet numbers go up while also using SUMIF.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Re: How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Re: How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Re: How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Re: How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Re: How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Re: How can I drag a formula and have the sheet numbers go up while also using SUMIF.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions