Sumif Formula using Cell content to refer to sheetname

Smithsat34

Board Regular
Joined
Jan 20, 2014
Messages
54
Board,

I have a three worksheets in a workbook, each one contains a months worth of data. They are titled Aug 16, Sep 16 & Oct 16.
I have a single summary sheet with a few sumif formulas. Ideally I want to be able to change a single cell on my summary sheet to reflect which month the sumif should look at. I have seen something like this elsewhere but just cannot find it.
Formula as follows:

summary sheet Cell C1 Sep 16

summary sheet Cell E4 =sumif('C1'!$P:$P,summary!a4,'C1'$K:$K)

Any suggestions to avoid 'find and replace' in the formula gratefully received
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try if the following formula works:

=SUMIF(INDIRECT("'" & $C$1 & "'!$P:$P"),A4,INDIRECT("'" & $C$1 & "'!$K:$K"))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,932
Messages
6,122,334
Members
449,077
Latest member
Jocksteriom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top