I am not able to increment my range used in formula that includes COUNTIF and INDIRECT

0
votes

I am using this formula =COUNTIF(INDIRECT("'"&$G$5&"'!E12:O12"),4) that counts no. of cells that has value 4 Ranging from E12 to O12 based on the Sheet I select in cell G5. But when I drag this formula to below cell range remains same. It is not incrmenting to E13:O13. Is there any way to achieve it ?

2
excelexcel-formulacountifexcel-indirect

2 Answers

1
votes

Pull the row number outside the INDIRECT string and concatenate:

=COUNTIF(INDIRECT("'"&$G$5&"'!E" & ROW($ZZ1) - 1 + 12 & ":O" & ROW($ZZ1) - 1 + 12),4)
0
votes

I would avoid INDIRECT function as much as possible for the large quantity of data since it slows down the excel spreadsheet. Ideal situation would be to calculate separately in each sheet using the formula below then bring the data to your main sheet using simple linking based on the condition.

=COUNTIF(OFFSET(E12,0,0,1,11),4)

If you still insist on INDIRECT, below is an alternative to previous answer.

=COUNTIF(OFFSET(INDIRECT("'"&$G$5&"'!E"& ROW(A12)),0,0,1,11),4)