CODEWITHSUNDEEP
excelexcel-formula
NinjaCat
NinjaCat

Reputation: 10194

How to use a cell reference in INDIRECT function

I have several cells that calculate totals from a single cell from multiple worksheets, in this case "Y3" from each of the sheets that are in Week, where n is an integer from 1 through 10:

=SUMPRODUCT(COUNTIF(INDIRECT("'Week"&{1,2,3,4,5,6,7,8,9,10}&"'!Y3"),"W"))

I would like to have a cell in my workbook that contains, A1:

1,2,3,4,5,6,7,9,10

So that way I can update only one cell when I add a worksheet to be counted. I've tried a few things and nothing seems to work.

Upvotes: 1

Views: 167

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Your formula : =SUMPRODUCT(COUNTIF(INDIRECT("'Week"&{1,2,3,4,5,6,7,8,9,10}&"'!Y3"),"W"))

This formula only work if you have 10 sheets with named Week1,Week2,Week3….Week10

But if you have less than 10 sheets e.g. Week1,Week2,Week3….Week9, your formula will fail and return "#REF!" error

The formula workaround is wrapping with a IFERROR() and become :

=SUMPRODUCT(IFERROR(COUNTIF(INDIRECT("'Week"&{1,2,3,4,5,6,7,8,9,10}&"'!Y3"),"W"),0))

However, if you wanted the formula acting dynamic and in A1 enter : 1,2,3,4,5,6,7,9,10

But 1,2,3,4,5,6,7,9,10 is a text string, you need converted it to an array. Using FILTERXML() can do the work of which available since Excel 2013

This is an array formula you need to confirm by pressing CTRL+SHIFT+ENTER instead of just ENTER:

=SUM(IFERROR(COUNTIF(INDIRECT("'Week"&FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b")&"'!Y3"),"W"),0))

EDIT 1 :

And,

if you haven't Excel 2013, you could use this longer array (CSE) formula instead

=SUM(IFERROR(COUNTIF(INDIRECT("'Week"&TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",))+1))*99,99))&"'!Y3"),"W"),0))

After that,

you can make adjustment to A1 number without changing formula contents

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53126

AFAIK you can't do that with a formula if you place the sheet list in a single cell I stand corrected: see bosco_yip's answer! (although you could with a UDF).

But, if you place your sheet list in a column, you can do this (using OFFSET, the list is dynamic, but must be the only data in that column. There are alternatives if that doesn't suit you):

=SUMPRODUCT(COUNTIF(INDIRECT("'Week"&TRANSPOSE(OFFSET(A1,1,0,COUNTA(A:A)-1,1))&"'!Y3"),"W"))

enter image description here

Applying the FILTERXML method, to create a non-array formula

=SUMPRODUCT(COUNTIF(INDIRECT("'Week"&FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","//b")&"'!Y3"),"W"))

Upvotes: 1

Related Questions