Forum Discussion
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.
Hi, Caps1255
Use the COUNTA formula - counts non-empty cells
e.g.: =COUNTA('03012024:0812023'!N5)
Regards,
Sergiusz
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.1 Date Sheets Value 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
