How to get reference from a cell and use it in a formula

Hi.

If you put a list of all your sheet names in a vertical range somewhere in your master sheet, e.g. G1:G3, then your formula is:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G3&"'!B2"),2))

which will count the total number of times that cell B2 contains a value of 2 across all 3 sheets listed in G1:G3.

Regards

You can also use INDIRECT(address) in your formula, for instance:

Please Login or Register  to view this content.

etc.

Originally Posted by ijl0322

WOW. You are such a genius!!! I can't believe this can be done with such a simple formula. I guess I have so much more to learn about excel.

May I ask, how does this formula achieve what I was trying to do??

Thank you so much!!!!!

Sorry - not clear whose post you're replying to!

Regards

Hi guys,

Thank you all for your help!! I really appreciate it!

Originally Posted by XOR LX

Sorry - not clear whose post you're replying to!

Regards

Sorry, I was actually replying to your post. When I was replying there was only one post..

Your formula works magic but I can't understand it's logic at all. Will you please explain how it's done for me??

Thank you so much!! I really appreciate your help!!

Sure.

If you're not familiar with formula techniques for working across multiple worksheets, you should make note of this construction using INDIRECT, as it offers practically the only means by which we can perform such calculations.

Substituting in the values from G1:G3 (i.e. your sheet names), it becomes:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"BIN1";"BIN2";"BIN3"}&"'!B2"),2))

This array of text strings {"BIN1";"BIN2";"BIN3"} representing your sheet names is then concatenated at both ends, at the back with a syntactically-correct construction representing the cell to be queried (i.e. B2 - note the preceding exclamation mark) and at the front with a single apostrophe. Although this latter is not strictly necessary should the names of the relevant worksheets contain no spaces (as in your case), it is nevertheless good practice to form the construction so.

Hence, after these concatenations, we will have:

=SUMPRODUCT(COUNTIF(INDIRECT({"'BIN1'!B2";"'BIN2'!B2";"'BIN3'!B2"}),2))

and, by virtue of the two facts that COUNTIF is able to operate over 3-D ranges (i.e. multiple worksheets) and that SUMPRODUCT is providing the necessary coercion such that INDIRECT will return an array of range references, not just one, this resolves to:

=SUMPRODUCT({0;1;1})

the elements of this array - 0, 1 and 1 - corresponding to the number of entries in B2 having a value of 2 in BIN1, BIN2 and BIN3 respectively. And so this finally resolves to 2, as required.

Hope that helps!

Regards

Originally Posted by XOR LX

Sure.

If you're not familiar with formula techniques for working across multiple worksheets, you should make note of this construction using INDIRECT, as it offers practically the only means by which we can perform such calculations.

Substituting in the values from G1:G3 (i.e. your sheet names), it becomes:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"BIN1";"BIN2";"BIN3"}&"'!B2"),2))

This array of text strings {"BIN1";"BIN2";"BIN3"} representing your sheet names is then concatenated at both ends, at the back with a syntactically-correct construction representing the cell to be queried (i.e. B2 - note the preceding exclamation mark) and at the front with a single apostrophe. Although this latter is not strictly necessary should the names of the relevant worksheets contain no spaces (as in your case), it is nevertheless good practice to form the construction so.

Hence, after these concatenations, we will have:

=SUMPRODUCT(COUNTIF(INDIRECT({"'BIN1'!B2";"'BIN2'!B2";"'BIN3'!B2"}),2))

and, by virtue of the two facts that COUNTIF is able to operate over 3-D ranges (i.e. multiple worksheets) and that SUMPRODUCT is providing the necessary coercion such that INDIRECT will return an array of range references, not just one, this resolves to:

=SUMPRODUCT({0;1;1})

the elements of this array - 0, 1 and 1 - corresponding to the number of entries in B2 having a value of 2 in BIN1, BIN2 and BIN3 respectively. And so this finally resolves to 2, as required.

Hope that helps!

Regards

Hi, thank you so much for your reply!!

Sorry, one more question. Right now, the cells that have no value across all three sheets appear as 0. If I would like them to appear as blanks, what can I do?? ( I’m trying to differentiate the ones with no value at all and the ones with no twos. )


Also, I’m trying to use this formula on 6700 other cells, but excel auto-fills it with =SUMPRODUCT(COUNTIF(INDIRECT("'"&G2:G4&"'!B2"),2)) ,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&G3:G5&"'!B2"),2))…rather than

=SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G3&"'!B3"),2)),

=SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G3&"'!B4"),2))….. Is there anything I can do about this??

Sorry that I have so many questions!! I'm really bad at it..

Thanks so much, I really appreciate it!!

Hi.

Change the formula in your first cell (that which refers to B2) to:

=IFERROR(1/(1/(SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!"&CELL("address",B2)),2)))),"")

When you copy this down, the reference to B2 will become, successively, B3, B4, etc., whilst the reference to the range G2:G4 will remain fixed.

Regards

Not at all. Happy to explain.

It's pretty much similar, apart from the fact that we now have this part:

CELL("address",B2)

which simply returns a text representation of a cell reference. Here, The above will resolve to $B$2.

Unlike in the previous formula I gave you, however, in which this value was fixed, here it is dynamically dependent upon the row in which the formula is placed.

Hence, as it is copied down to successive rows, the above will become:

CELL("address",B3) (=$B$3)
CELL("address",B4) (=$B$4)
...

etc.

Hence:

SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!"&CELL("address",B2)),2))

becomes:

SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!"&"$B$2"),2))

and I explained how this was resolved in my previous post.

The only other addition was to satisfy your criterion of returning a blank instead of a zero. The standard way to do this would be to use a simple IF statement and repeat the main clause, i.e.:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!"&CELL("address",B2)),2))=0,"",SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!"&CELL("address",B2)),2)))

which is perfectly fine, and if this makes things easier for you to understand I would suggest you use this version instead.

The set-up I used simply employs a technique to shorten the formula, which takes advantage of the fact that the only number which results in an error when reciprocated with 1 is zero, so that, if the part:

SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!"&CELL("address",B2)),2))

is equal to zero, then we will have:

=IFERROR(1/(1/(0)),"")

which is:

=IFERROR(1/(#DIV/0!),"")

i.e.:

=IFERROR(#DIV/0!,"")

(1 divided by #DIV/0! is still #DIV/0!)

which of course resolves to "", i.e. a blank.

If the main SUMPRODUCT part does not resolve to zero, but e.g. 2, then we will have:

=IFERROR(1/(1/(2)),"")

which is:

=IFERROR(1/(0.5),"")

i.e.:

=IFERROR(2,"")

and clearly 2 is not an error so this returns 2.

This is the reason for the "double-reciprocation": errors remain errors; non-errors are "re-reciprocated" back to their original values.

See here for more on this technique:

http://excelxor.com/2014/08/29/iferr...-from-results/

Regards

You're welcome! Glad I could help.

Cheers