ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   SUM and Frequency for Multiple SHeets (https://www.excelbanter.com/excel-worksheet-functions/252170-sum-frequency-multiple-sheets.html)

BobbyRT

SUM and Frequency for Multiple SHeets
 
Hi....in sheet 1, I have a column that has unique numbers and using the
following formula to lookup the field in sheet 46500! Column H and then count
the unique frequencies in sheet 46500! column A.

=SUM(1*(FREQUENCY(IF(('46500'!$H$2:$H$43207<"")*( '46500'!$H$2:$H$43207=$A4),'46500'!$A$2:$A$43207), '46500'!$A$2:$A$43207)0))

How do I change that formula so that it also reads additional sheets (46501,
47400, 43100, 43103, 47300) and only counts the unique combination like the
formula above does for one sheet? Please Help


T. Valko

SUM and Frequency for Multiple SHeets
 
You can't reference multiple sheets with that formula. No way to do this for
conditional uniques that I know of.

--
Biff
Microsoft Excel MVP


"BobbyRT" wrote in message
...
Hi....in sheet 1, I have a column that has unique numbers and using the
following formula to lookup the field in sheet 46500! Column H and then
count
the unique frequencies in sheet 46500! column A.

=SUM(1*(FREQUENCY(IF(('46500'!$H$2:$H$43207<"")*( '46500'!$H$2:$H$43207=$A4),'46500'!$A$2:$A$43207), '46500'!$A$2:$A$43207)0))

How do I change that formula so that it also reads additional sheets
(46501,
47400, 43100, 43103, 47300) and only counts the unique combination like
the
formula above does for one sheet? Please Help




BobbyRT

SUM and Frequency for Multiple SHeets
 
Is there another way to go about doing this?

"T. Valko" wrote:

You can't reference multiple sheets with that formula. No way to do this for
conditional uniques that I know of.

--
Biff
Microsoft Excel MVP


"BobbyRT" wrote in message
...
Hi....in sheet 1, I have a column that has unique numbers and using the
following formula to lookup the field in sheet 46500! Column H and then
count
the unique frequencies in sheet 46500! column A.

=SUM(1*(FREQUENCY(IF(('46500'!$H$2:$H$43207<"")*( '46500'!$H$2:$H$43207=$A4),'46500'!$A$2:$A$43207), '46500'!$A$2:$A$43207)0))

How do I change that formula so that it also reads additional sheets
(46501,
47400, 43100, 43103, 47300) and only counts the unique combination like
the
formula above does for one sheet? Please Help



.


T. Valko

SUM and Frequency for Multiple SHeets
 
Is the range A2:A43207 on each of the 6 sheets to be considered a single
range? If so, I don't know how you'd do this.

You're testing 43K rows per sheet for a total of 6*43K = 250K rows.
Counting conditional uniques in that many rows might cause things to grind
to a halt.

--
Biff
Microsoft Excel MVP


"BobbyRT" wrote in message
...
Is there another way to go about doing this?

"T. Valko" wrote:

You can't reference multiple sheets with that formula. No way to do this
for
conditional uniques that I know of.

--
Biff
Microsoft Excel MVP


"BobbyRT" wrote in message
...
Hi....in sheet 1, I have a column that has unique numbers and using the
following formula to lookup the field in sheet 46500! Column H and then
count
the unique frequencies in sheet 46500! column A.

=SUM(1*(FREQUENCY(IF(('46500'!$H$2:$H$43207<"")*( '46500'!$H$2:$H$43207=$A4),'46500'!$A$2:$A$43207), '46500'!$A$2:$A$43207)0))

How do I change that formula so that it also reads additional sheets
(46501,
47400, 43100, 43103, 47300) and only counts the unique combination like
the
formula above does for one sheet? Please Help



.





All times are GMT +1. The time now is 04:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com