# COUNTIF across multiple worksheets

This is a discussion on COUNTIF across multiple worksheets within the Excel Questions forums, part of the Question Forums category; Would someone know what I am doing wrong here? I need to count the frequency/occurance of letters "A", "B", "C" ...

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

2. 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)

_________________

4. For more on morefunc, see Function Descriptions

5. [ This Message was edited by: Aladin Akyurek on 2003-01-26 17:12 ]

6. 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

7. 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 :

******** ******************** ************************************************************************>
 Microsoft Excel - 178.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D1D2D3 =

A
B
C
D
E
1
JAAFAR**4*
2
H**3*
3
100**3*
4
F1****
5
10****
6
0.5****
7
A****
8
COBOS****
9
JAAFAR****
10
F****
 sales *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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 ]

8. ## Re: COUNTIF across multiple worksheets

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

9. ## Re: COUNTIF across multiple worksheets

How would you add range H:H with "B" as criteria and range AC:AC with "C"

10. ## Re: COUNTIF across multiple worksheets

Originally Posted by Jeffy14
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
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"))

11. ## Re: COUNTIF across multiple worksheets

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

12. ## Re: COUNTIF across multiple worksheets

Originally Posted by Jeffy14
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
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.

13. ## Re: COUNTIF across multiple worksheets

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

Page 1 of 4 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•