COunt Unique Text Values Across Multiple Worksheets

[activeman]

I want to count the number of unique text values that appear in the same column but over four separate worksheets.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
So far I have found the following formula which works for a single worksheet but obviously cannot sum this across multiple worksheets as each formula will not take account of the unique values counted in the previous formula.<o></o>
<o> </o>
=SUM(IF(FREQUENCY(MATCH('Sheet1'!DH:DH,'Sheet1'!DH:DH,0),MATCH('Sheet1'!DH:DH,'Sheet1'!DH:DH,0))>0,1))

(With DH being the column I am counting in each Worksheet)
<o> </o>
It seems like a simple thing I am trying to do but the Excel requirement seems more involved.<o></o>

Does anyone know the answer to this?

 

[BinduVardhini]

Hi Dave,

I was using the above function as below but it is not working. Please suggest me the solution please

=MultiSheetUniqueCount(Sheet3:Sheet28,F4:F)

Basically my Requirement is need to create a Separate Sheet which should have the below two Columns

1) First Column - In need to fetch the Unique names from Column B in all the sheets and show it here
2) Second Column - I need to check Column B along with Column F in all the sheets here. We need to show the Count of Distinct Value of Column B which has test 'FAIL' in column F.

Example

Sheet 1

Column B ---- Column F
TABLE 1 ---- PASS
TABLE 1 ----- FAIL
TABLE 2 ----- PASS
TABLE 3 ----- PASS
TABLE 4 ----- FAIL
TABLE 4 ----- PASS
TABLE 4 ----- FAIL

In need to have Separate Sheet which should be like this, the Count Should be based on 'FAIL' in Column F as in above

TABLE1 - 1
TABLE2 - 0
TABLE3 - 0
TABLE4 - 2

We have to create the logic based on Multiple sheets, just as an Example I have mentioned only one sheet.

Thanks!
Bindu

 

[pratapnaidums]

bump.

I was just looking for the same solution, can anybody help me to provide the dynamic sheet, combining 3 cells:

A1 cell has ARA
A2 cell has DRA
A3 cell has SWR

currently I was just hardcoding the values....
=SUMPRODUCT((ARM_DRA_SWR!B1:B6000<>"")/COUNTIF(ARM_DSR_SWRD!B1:B6000,ARM_DSR_SWRD!B1:B6000&""))

Help is really appreciated...!!! Have a nice day

 

[get_pchelp]

This VB code looks good to me,except that I need it to make it more dynamic, in the sense I want to search in different ranges in different sheets as shown below:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Sheet1[/TD]
[TD="width: 64"]A2:A7[/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD]A3:A8[/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD]B10:B20[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know what changes I need to make in VB code to achieve this?