Hello,
I am in need of a formula, which I think may be quite straightforward however, I cannot get the result I want.
In sheet 1, column B I have a list of strings, they are also located in column B in sheet 2.
In sheet 1, column G, each string has an associated number in date format 01/01/2010. Only the year is of interest.
Date values can go on for multiple rows in column G in Sheet 1, such that there are can be empty rows between the strings in Column B. In Column L in Sheet 2, I want for each string name, the number of occurrences of each specified year, such that the number 3 would be returned in the below example for 2010.
Example:
Sheet 1
Column B Column G
Name1 10/01/2010
19/20/2010
01/01/2010
Name2 .........
I have tried the following formula as a start, it is not quite working though, it correctly returns 1 for strings that had a 2010, but not multiple 2010's.
=COUNTIF(INDEX(Sheet1!G:G;MATCH(B2;Sheet1!B:B
);"*"&2010&"*")
Thank you!
I am in need of a formula, which I think may be quite straightforward however, I cannot get the result I want.
In sheet 1, column B I have a list of strings, they are also located in column B in sheet 2.
In sheet 1, column G, each string has an associated number in date format 01/01/2010. Only the year is of interest.
Date values can go on for multiple rows in column G in Sheet 1, such that there are can be empty rows between the strings in Column B. In Column L in Sheet 2, I want for each string name, the number of occurrences of each specified year, such that the number 3 would be returned in the below example for 2010.
Example:
Sheet 1
Column B Column G
Name1 10/01/2010
19/20/2010
01/01/2010
Name2 .........
I have tried the following formula as a start, it is not quite working though, it correctly returns 1 for strings that had a 2010, but not multiple 2010's.
=COUNTIF(INDEX(Sheet1!G:G;MATCH(B2;Sheet1!B:B
);"*"&2010&"*")Thank you!
