Forum Discussion
Excel formula help!
Hi Tracie, I see you tagged '365' so you have access to VSTACK. This will make things easier and INDIRECT is not needed.
First, use VSTACK to combine all the sheets by supplying a 3D reference. Next, determine if the 'input' (the cell containg the text) is equal to the 'stack' (the combined ranges). The result will be a matrix of TRUE and FALSE. N converts those to 1s and 0s then SUM adds them up.
=LET( stack, VSTACK(Sheet1:Sheet3!$A$1:$J$30), SUM(N(input = stack)) )
Hi Tracie, I see you tagged '365' so you have access to VSTACK. This will make things easier and INDIRECT is not needed.
First, use VSTACK to combine all the sheets by supplying a 3D reference. Next, determine if the 'input' (the cell containg the text) is equal to the 'stack' (the combined ranges). The result will be a matrix of TRUE and FALSE. N converts those to 1s and 0s then SUM adds them up.
=LET(
stack, VSTACK(Sheet1:Sheet3!$A$1:$J$30),
SUM(N(input = stack))
)
- Glad it worked. Have a great day!