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
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