COUNTIF Question

[imback2nite]

Thank you in advance for looking at this!
I'm using this formula to count how many times cell H3 value appears in a range. I'm using Excel 2003 so COUNTIFS is not an option.
=COUNTIF($B$12:$AS$86,$H3)
There are 30 pages I would like this to evaluate. They are Sheet13 to Sheet42.
Is there a way to do this? Right now I'm using
=COUNTIF($B$12:$AS$86,$H3)
on the individual pages.

 

[Jamie McMillan]

Hello,

I am not sure why "COUNTIFS is not an option" I am sure I used CountIf in 2003; however the below code will do the same.

You need to add, Sheet16 - Sheet42 & the count total goes into cell D1

Sub EachSheet()
    Application.ScreenUpdating = False
    Sheets("Sheet13").Select ' select each sheet and calls the macro below
    Call CountIfSame
    Sheets("Sheet14").Select
    Call CountIfSame
    Sheets("Sheet15").Select
    Call CountIfSame
    Sheets("Sheet1").Select 'returns to the original sheet
    Application.ScreenUpdating = True
End Sub
Sub CountIfSame()
    Dim i As Integer, j As Integer
    i = 1
    j = 0
    For i = 1 To 20 'loops through rows 1 to 20 column A
    If Cells(i, 1).Value = Cells(3, 8).Value Then 'if each cell = H3 then counts 1
    j = j + 1
    End If
    Next i
    Range("d1").Value = j 'The total count is put in cell d1 on each sheet
End Sub

Jamie

 

[gordsky]

you dont say if H3 value is same accross all sheets?

If your countif statements are in the same cell on every sheet (say cell a1) you can use this

=COUNT(Sheet1:Sheet2!A1) <--- change sheet1 to be your first sheet name and sheet2 to be your last sheet name

alternatively a workaround would be create an index of all your sheet names on a new sheet in column a.

you could then use the following formula to add all

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A2&"'!$B$12:$AS$86"),$H$3)) <--change A1:A2 to be the range of your list of sheet names

 

[Fluff]

I am not sure why "COUNTIFS is not an option" I am sure I used CountIf in 2003;

COUNTIF & COUNTIFS are different functions the latter only appeared in 2007.

@imback2nite if you are using 2003 you might want to change you profile to reflect that.

 

[imback2nite]

COUNTIF & COUNTIFS are different functions the latter only appeared in 2007.

@imback2nite if you are using 2003 you might want to change you profile to reflect that.

Thank you. I have been using 2007 but the persons that will be using this workbook will be using version 2003 so I have to create this workbook with the version 2003 limitations.

 

[imback2nite]

Sorry it took awhile to get back. I was yet another victim of Covid. Whew! I was not a pretty sight! @@
Hats off to both suggestions! They actually both work very welI! I'm going to implement them both and see which works best.
Once again my apologies for my tardy reply.