Modifying a SUMPRODUCT formula.

[dougmarkham]

Hi Folks,

I have the following worksheets: ‘Table' worksheet and ‘data1’ worksheet.
I am looking to design a formula to 'count the number of unique values' in column A of the 'data1' worksheet.

The ‘Table Worksheet’ contains ‘table 1’ with ‘column A’ listing my worksheet names. My goal is to put a formula in column B of the ‘table 1’ that refers to ‘worksheet names’ within column A of ‘table 1’ and then calculates the number of unique values in Column A of the worksheet being referred to (column A in data1 has cell A1 as a header which I do not wish to be included in the count).

E.g. in Table 1: if cell A2 contains ‘Data1’ (the name of the first worksheet), in B2 I would like to put in a formula that reads the contents of cell A2 (‘Data1’) and refers to the worksheet data1 before calculating and returning the number unique values in column A of the ‘Data1’ worksheet.

I found a formula for calculating the number of unique values within a column:

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

I also found a formula for referring to Cell A2 in Table 1

=INDIRECT(“’”&$A2&”’!”&A:A)

However, I have tried combining these formulas but cannot get it to work.
Any help would be much appreciated!

Kind regards,

Doug

 

[wideboydixon]

Like this?

Book1
	A	B
1	Sheet	Unique Values
2	Data1	4
3	Data2	6
Table

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((INDIRECT($A2&"!A:A")<>"")/COUNTIF(INDIRECT($A2&"!A:A"),INDIRECT($A2&"!A:A")&""))

WBD

 

[dougmarkham]

Like this?

	A	B
1	Sheet	Unique Values
2	Data1	4
3	Data2	6

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Table​

Worksheet Formulas Cell Formula B2 =SUMPRODUCT((INDIRECT($A2&"!A:A")<>"")/COUNTIF(INDIRECT($A2&"!A:A"),INDIRECT($A2&"!A:A")&"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WBD

That's done it, thanks WBD!!

In one attempt, I had tried:

=SUMPRODUCT(((INDIRECT("'"&A26&"'!A:A"))<>””)/COUNTIF((INDIRECT("'"&A26&"'!A:A")),(INDIRECT("'"&A26&"'!A:A"))&”))

so close yet so far...

Many thanks again!