I have column A of sheet 1 where there is lot of time data.
In sheet 2 I wanted to count the data if some condition is matching based in index I wanted to give. I’m doing it in Sheet 2.
For e.g. sometimes I need to count from 3rd cell to 20th cell of Col A of Sheet 1 like given below
COUNTIF(sheet1!A3:A20, "some condition")
and sometimes I wanted to count from 7th cell to 91st cell of Col A of Sheet 1 like
COUNTIF(sheet1!A7:A91, "some other condition")
Now, my problem has evolved and I have more cols in Sheet 1 (like Col B, Col C, etc.). As a result it becomes tedious task to change range index for each of them).
WHat I wanted is – if there is a way to put index values (startIndex and endIndex) in sheet 2.
So what I’m trying to do is to reference startIndex, endIndex and columnName to caluclate the count. And it is giving me error (formula parse error).
>Solution :
You may try:
=countif(indirect("Sheet1!"&B3&B1&":"&B3&B2),">20")
