Forum Discussion
Count occurrences across multiple sheets
Rodrigo_ Thank you, this is super helpful! Is there a way to make the range (A1:A100 in your example) dynamic? I'd like to copy the formula across multiple columns, and return the counts for columns B1:B100, then C1:C100, etc. across the same range of sheets.
AlyssaS
Welcome, use this formula to be able to change the reference when you drag or copied it across the columns.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A12&"'!"&ADDRESS(1,COLUMN(B1))&":"&ADDRESS(100,COLUMN(B1))), B1))
here's the breakdown:
COLUMN(B1) - It will dynamically gets the column number, when you drag,copy the whole formula across columns, this will update/adjust accordingly B1 becomes C1.
ADDRESS(1,COLUMN(B1)) - it serves as the starting cell for COUNTIF
ADDRESS(100,COLUMN(B1)) - end cell for COUNTIF
INDIRECT("'"&A1:A12&"'!"&ADDRESS(1,COLUMN(B1))&":"&ADDRESS(100,COLUMN(B1))) - This part constructs the dynamic range reference based on the ADDRESS function
COUNTIF(..., B1) - counts occurrences of the value of B1 within the given dynamic range on INDIRECT function.
- Thanks!
