Excel - Using COUNTIF COUNTIFS across multiple sheets same column

Question

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

User · Answer

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

User · Answer

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  lt  gt   Sheet1  And ws name  lt  gt   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

User · Answer

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     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     amp D3 D5 amp    A A    B2

User · Answer

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

[excel] Excel - Using COUNTIF/COUNTIFS across multiple sheets/same column

Examples related to excel

Examples related to vba