Help with a SUMPRODUCT to count data

[jshaw82]

Hi,

I had a SUMPRODUCT that I used to count holidays in a table and I've tried to put it into another workbook but i'm getting #value. What i'm trying to do is count the word "HOL" on a rota for an individual between a set time because the holiday year is different to the financial year and I need it to work for subsequent years.

This is the formula but i'm using names ranges so I have attached an example.

Please Login or Register  to view this content.

Any help as always appreciated

BOOK2.xlsx

John

[Pauleyb]

Not quite sure what your final intent is, but the problem is that your arrays are different sizes. Names is 3, the rest are 371, and TM_Shifts is actually 371x3 matrix (which could still work). The equation will count all of the HOL cells if you remove the TM_Names reference which is hosing up your matrix math.

[FDibbins]

You dont have a date in E1, but use this, and then add the extra criteria when you sort that out...

=COUNTIFS(OFFSET(Sheet2!$A$4,1,MATCH(Sheet1!A2,Sheet2!$A$4:$E$4,0)-1,COUNTA(Sheet2!A:A)-1,1),"HOL",TM_Dates,">="&Sheet1!D1)