yogi_Compute Number Of Instances Of Leave For Each Person Listed In D2:I2 For Time period Selected In Cell A1

Google Spreadsheet   Post  #2004

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2016

post by: Sricharan P:

https://productforums.google.com/forum/#!topic/docs/7vMWnouBdso;context-place=mydiscussions
Using SUMPRODUCT + SUBTOTAL + OFFSET with Google Spread Sheet

hello every one,

My task is to accomplish Count text based on Criteria (Countif) for only filtered Range

For that i tried Subtotal Function. But, Subtotal Function doesn't host Count if

As a substitute i found following formula

SUMPRODUCT(SUBTOTAL(3,OFFSET(F$2:F$2016,ROW(F$2:F$2016)-ROW(F$2),0,1)),--(F$2:F$2016="Leave"))

it works perfectly in Excel 2013

but, when the same is placed in Google Spread Sheet it shows the error as 

"SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1."

and returns the value as "#VALUE!" 

is there any means by which i can accomodate this formula into Google Spread Sheet. 

or else is there any substitute to this formula to place it in google Spread sheet.

thank you very much.

-------------------------------------------------------------------------------------------------------------------