Forum Discussion

Caps1255's avatar
Caps1255
Copper Contributor
Jan 30, 2024

Countif in multiple sheets

Hi all,

 

I have multiple sheets in which cell "N5" has one number from 1-13.

Can I do countif or any else formula to count how many times it has each number?

Thank you.

 

excel
Formulas and Functions
  • Sergiusz's avatar
    Sergiusz
    Copper Contributor
    Jan 30, 2024

    Hi, Caps1255 

    Use the COUNTA formula - counts non-empty cells
    e.g.: =COUNTA('03012024:0812023'!N5)

     

    Regards,

     

    Sergiusz

    • Caps1255's avatar
      Caps1255
      Copper Contributor
      Jan 31, 2024

      Sergiusz 

       

      I need data how many times was there each number.

      e.g.: How many times was there number 3

       

      • Sergiusz's avatar
        Sergiusz
        Copper Contributor
        Jan 31, 2024

        Hi, Caps1255 

        My intermediate solution is as follows:
        1. preparing a list of Sheets based on the date because I don't know it
        2. If data from other Cells are needed, I have prepared a variable in Cell E1 (yellow cells with red font, same as A2)
        3. the Cell values in each Sheet (specified in E1) will be shown in Column C
        4. From Table A:C we create a pivot table that shows how many Sheets a given number appears in
        5. To make things easier - I added a Slicer, where You can easily deselect from the PivotTable Sheets do not exist.



        1DateSheetsValue  

        2

        2024-01-03

        		=TEXT(A2;"DDMMYYYY")=IFERROR(INDIRECT("'"&B2&"'!"&$E$1;1);"no Sheet")Cell:N5
        3=A2-1 =TEXT(A3;"DDMMYYYY") =IFERROR(INDIRECT("'"&B3&"'!"&$E$1;1);"no Sheet")  
        4=A3-1 =TEXT(A4;"DDMMYYYY") =IFERROR(INDIRECT("'"&B4&"'!"&$E$1;1);"no Sheet")  
              

         

        Regards,
        Sergiusz

Resources