COUNTIF across multiple worksheets

S

slbutter1

New Member
Joined
Jan 25, 2003
Messages
1
Would someone know what I am doing wrong here? I need to count the frequency/occurance of letters "A", "B", "C" etc. that occur in the exact same cell in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query for me but gives the "VALUE" error message when I insert the sheet1:sheetxxx range.
Thank you,
Stephen Butter
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
COUNTIF is not applicable as a 3D function.

Insert 2 new worksheets and name them First and Last. Place the relevant worksheets between First and Last.

Now use:

=COUNTIF.3D(First:Last!Range,Condition)

This function is part of the morefunc.xll add-in which you need to download & install.

Addendum. Otherwise, follow Ogilvy's advise.


_________________<LI>Download morefunc</LI><LI>For more on morefunc, see Function Descriptions</LI>
This message was edited by Aladin Akyurek on 2003-01-26 17:12
 
Upvote 0
Hi

As far as I know Countif will not allow multi sheets selections, so no this can not be done as you request.

Im not sure how to get around this, but im very confident that this is not a built in function to excel as Countif is. So I assume VBA will be needed or UDF at the very least

Kind rdgs

Jack
 
Upvote 0
Hi Stephen,

Like Jack said,It is probably necessary to use a UDF.

I have created a UDF called CntIf3D which stands for 3 Dimensional CountIf.

The code is as follows:


Public Function CntIf3D(rng As Range, V As Variant, ParamArray arglist() As Variant)
Application.Volatile

CntIf3D = 0
For Each arg In arglist
CntIf3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + CntIf3D
Next

End Function


This UDFunction takes 2 Arguments like the Excel Built-in CountIf Function ( The Range and the value to be counted ) + an indefinite number of Optional arguments which represent each the name of each worksheet included in the count for their respective range.

Below is a sample worksheet using the Function in cells D1 Trough D3.
Note that the formulas refer to all the 4 sheets in the workbook namely : Sheets1,2,3 and "Sales".For eg Cell D1 returns 4 which is the total number of cells containing the letter "A" in Column(A)of all the worksheets in the workbook :
178.xls
ABCDE
1JAAFAR4
2H3
31003
4F1
510
60.5
7A
8COBOS
9JAAFAR
10F
sales



Note that The order of the worksheets inside the function is not important which makes the use of the Function very convinient.


Hope this helps.

Jaafar.
This message was edited by rafaaj2000 on 2003-01-26 20:30
This message was edited by rafaaj2000 on 2003-01-26 20:31
This message was edited by rafaaj2000 on 2003-01-26 20:33
 
Upvote 0
Jaffar Tribak, great formula for going accross multiple sheets!
However I have a question:

Your Code helps me going through multiple sheets but I cant enter mutplite criterias and ranges as with the formula COUNTIFS

For example, with CONTIFS,you can add muptiple criterias and ranges like so:
=+COUNTIFS(A:A;"A";H:H;"B";AC:AC;"C")

You see ?
I need to include the Range A:A , H:H and AC:AC through mutplie sheets to find the value "A" , "B" and "C"

The formula you did allows to do though multiple sheets; but how would you go add to this mutpliple criteria and range please ?

thanks a lot for your help
 
Upvote 0
Your formula gives this =CntIf3D(A:A;"A";"Sheet1";"Sheet2";"Sheet3")
How would you add range H:H with "B" as criteria and range AC:AC with "C"
 
Upvote 0
Jeffy14 said:
Jaffar Tribak, great formula for going accross multiple sheets!
However I have a question:

Your Code helps me going through multiple sheets but I cant enter mutplite criterias and ranges as with the formula COUNTIFS

For example, with CONTIFS,you can add muptiple criterias and ranges like so:
=+COUNTIFS(A:A;"A";H:H;"B";AC:AC;"C")

You see ?
I need to include the Range A:A , H:H and AC:AC through mutplie sheets to find the value "A" , "B" and "C"

The formula you did allows to do though multiple sheets; but how would you go add to this mutpliple criteria and range please ?

thanks a lot for your help
Click to expand...

Create a range housing the relevant sheet names, name this range SheetList, and invoke:

Code: 
=SUMPRODUCT(COUNTIFS(
   INDIRECT("'"&SheetList&"'!A:A"),"A",
   INDIRECT("'"&SheetList&"'!H:H"),"B",
   INDIRECT("'"&SheetList&"'!AC:AC"),"C"))
 
Upvote 0
thx a lot for your answer Aladin Akyurek.
However, I'm gonna ask a really stupid quesiton but how to create a range having multiple sheets ???
lets say I have only Sheet1 and Sheet2

SheetList= ??

thanks a lot
 
Upvote 0
Jeffy14 said:
thx a lot for your answer Aladin Akyurek.
However, I'm gonna ask a really stupid quesiton but how to create a range having multiple sheets ???
lets say I have only Sheet1 and Sheet2

SheetList= ??

thanks a lot
Click to expand...

Insert a sheet, name it Admin. Go to Admin, insert Sheet1 in A2 and Sheet2 in A3. Select A2:A3, go the Name Box on the Formula Bar, type SheetList, and hit the enter key.
 
Upvote 0
Hi Aladin,
Thanks for your answer!
However, you will probably think I'm a retard but I dont understand what you want me to do:

http://img94.imageshack.us/i/excelf.jpg/
thats what you want me to do ?
then I I go in the formula bar to put SheetList it changes the Cell name in A2, that is Sheet1

I'm not sure that's what you meant
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
190
Domenic
D
F
  • Question Question
CountIf Across Dynamic Range of Worksheets
Replies
3
Views
118
franswa3434
F
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
327
Peter_SSs
Peter_SSs
P
  • Question Question
Counting characters across multiple worksheets
Replies
5
Views
330
Fluff
Fluff
M
  • Question Question
Unique entries across multiple worksheets
Replies
4
Views
282
m_smith_solihull
M

Forum statistics

Threads
1,224,503
Messages
6,179,136
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top