Need formula to read list of sheet names for COUNTIF

[djl0525]

On sheet named References, I have the following entries in A5:A7.
Ex 12.1.1 Create PivotTables, (p. 462)
Ex 12.1.2 Format a PivotTable, (p. 466)
Ex 12.1.3 Change PivotTable Fields, (p. 467)

I have sheet names that correspond to the numeric references in these entries.
12.1.1
12.1.2
12.1.3

On the References sheet, I have formulas in column C.
=COUNTIF('12.1.1'!D:D,"*sponsors*")
=COUNTIF('12.1.2'!D:D,"*sponsors*")
=COUNTIF('12.1.3'!D:D,"*sponsors*")

I need up modify this formula so it captures the sheet name from my entries (12.1.1, 12.1.2, 12.1.3, etc.). Then I can copy it to many rows in column C.

Any assistance will be appreciated.

DJ

 

[jorismoerings]

take a look at this:

Excel 2016 (Windows) 64 bit
	A	B
5	Ex 12.1.1 Create PivotTables, (p. 462)
6	Ex 12.1.2 Format a PivotTable, (p. 466)
7	Ex 12.1.3 Change PivotTable Fields, (p. 467)
8
9		3
Sheet4

Cell Formulas
Range	Formula
B9	=SUMPRODUCT(COUNTIF(INDIRECT("'"&MID(A5:A7,4,6)&"'!d:d"), "Sponsors"))

 

[djl0525]

Thank you so much. I changed A5:A7 to just A5 and the copy and paste was successful.

I really appreciate your help!!

DJ

 

[jorismoerings]

Glad i Could help.