6

I am trying to "COUNT" the number of a certain object in column I (in this instance) across multiple sheets. That value in column I is the result of a formula (if it matters). So far I have:

=COUNTIF('Page M904'!I:I,A13)+COUNTIF('Page M905'!I:I,A13)+COUNTIF('Page M906'!I:I,A13)

which works, but I am going to have 20 something pages to scan through. I would like to avoid having a page long formula.

I have tried

=COUNTIFS('Page M904:Page M906'!I:I,A13) and =COUNTIF('Page M904:Page M906'!I:I,A13)

but that results in a #VALUE.

And I think

=COUNTIFS('Page M904'!I:I,A14,'Page M905'!I:I,A14,'Page M906'!I:I,A14)

is a misapplication of the COUNTIFS because I get 0 when it should be 35.

I am trying to avoid using VBA for this application. But if has to be, then it has to be :) Thanks in advance for your time and help.

Improve this question
5
  • 2
    'Page M904:Page M906'!I:I - good attempt, but unfortunatelly Countif doesn't support 3-D references.. Commented Feb 20, 2014 at 21:01
  • 1
    I am trying to avoid using VBA. But if has to be, then it has to be:) - you could write quite simple user defined function for this. Commented Feb 20, 2014 at 21:02
  • 1
    =COUNTIFS('Page M904'!I:I,A14,'Page M905'!I:I,A14,'Page M906'!I:I,A14) returns 0 because in that phrasing it becomes IF...AND, rather than IF...OR as you want it. Commented Feb 20, 2014 at 21:06
  • Thanks everyone. The UDF is actually new to me, but I am still a noob after 20 years ;) Everyone here is great with their insight and tolerance of beginner level questions :D Commented Feb 20, 2014 at 21:16
  • You could simplify the formula a bit by Naming the Columns of those sheets. Say M904i for col I of Page M904. So 'Page M904'!I:I becomes M904i. But still, there are 20+ of this naming. Commented Feb 20, 2014 at 22:47

4 Answers 4

Reset to default
11

This could be solved without VBA by the following technique.

In this example I am counting all the threes (3) in the range A:A of the sheets Page M904, Page M905 and Page M906.

List all the sheet names in a single continuous range like in the following example. Here listed in the range D3:D5.

enter image description here

Then by having the lookup value in cell B2, the result can be found in cell B4 by using the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D3:D5&"'!A:A"), B2))
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Søren, but I am going with the VBA/UDF method. I didn't know about UDFs but now I do and it seems simpler. I definitely appreciate the assistance. :D
5

I am trying to avoid using VBA. But if has to be, then it has to be:)

There is quite simple UDF for you:

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
    Next ws
End Function

and call it like this: =myCountIf(I:I,A13)

P.S. if you'd like to exclude some sheets, you can add If statement:

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.name <> "Sheet1" And ws.name <> "Sheet2" Then
            myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
        End If
    Next ws
End Function

UPD:

I have four "reference" sheets that I need to exclude from being scanned/searched. They are currently the last four in the workbook

Function myCountIf(rng As Range, criteria) As Long
    Dim i As Integer

    For i = 1 To ThisWorkbook.Worksheets.Count - 4
        myCountIf = myCountIf + WorksheetFunction.CountIf(ThisWorkbook.Worksheets(i).Range(rng.Address), criteria)
    Next i
End Function
Improve this answer

4 Comments

I have four "reference" sheets that I need to exclude from being scanned/searched. They are currently the last four in the workbook and will remain in that place. Can that code be changed to exclude those four sheets?
stupid question... how do I get the UDF to work. I get a #NAME? error. Macros are enabled.
you should create new module and place UDF in it. How to create module see in this image: webum.by/images/blog/2013/05/excel-vba-access-bd/…
My first response would've been "What is this 'module' you speak of?" but I saw the pic, followed that and it WORKS! Many thanks and respek.
2

My first post... UDF I managed quickly to compile. Usage: Select 3D range as normal and enclose is into quotation marks like below...

=CountIf3D("'StartSheet:EndSheet'!G16:G878";"Criteria")

Advisably sheets to be adjacent to avoid unanticipated results.

Public Function CountIf3D(SheetstoCount As String, CriteriaToUse As Variant)

     Dim sStarSheet As String, sEndSheet As String, sAddress As String
     Dim lColonPos As Long, lExclaPos As Long, cnt As Long

    lColonPos = InStr(SheetstoCount, ":") 'Finding ':' separating sheets
    lExclaPos = InStr(SheetstoCount, "!") 'Finding '!' separating address from the sheets

    sStarSheet = Mid(SheetstoCount, 2, lColonPos - 2) 'Getting first sheet's name
    sEndSheet = Mid(SheetstoCount, lColonPos + 1, lExclaPos - lColonPos - 2) 'Getting last sheet's name

    sAddress = Mid(SheetstoCount, lExclaPos + 1, Len(SheetstoCount) - lExclaPos) 'Getting address

        cnt = 0
   For i = Sheets(sStarSheet).Index To Sheets(sEndSheet).Index
        cnt = cnt + Application.CountIf(Sheets(i).Range(sAddress), CriteriaToUse)
   Next

   CountIf3D = cnt

End Function
Improve this answer

Comments

0

I was looking to do the same thing, and I have a work around that seems to be less complicated using the Frequency and Index functions. I use this part of the function from averaging over multiple sheets while excluding the all the 0's.

=(FREQUENCY(Start:End!B1,-0.000001)+INDEX(FREQUENCY(Start:End!B1,0),2))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.