Hello All,
I'm a bit stumped on how to solve a particular data problem and was hoping for a nod in the right direction.
I have a table, which along the top going across columns has some unique codes in row 1, and some categories attached to those codes in row 2, which are populated using a vlookup. The number of rules/columns across the top varies, although I can fix the formula to go to from A-Z. Each unique code can have the same category attached to it as another code in row 2.
I have dates going down the table in column A.
Within the table, I have some numbers which are populated using countifs based on the code in row 1 and the date in column A. Essentially, they are counting the number of times a warning against a particular code is flagged in a separate data sheet in a given month.
I need to write a formula, that counts if there has been any 'non-zero' entries in the data table, associated with a particular category, between two dates.
For example:
I want to count the number of rules that have been broken relating to the 'IMA' category, between 31/01/2016 and 29/02/2016:
So in the above, there are two rules with the category 'IMA' in row 2. Between the dates of Jan-Feb 2016 (column A), both of these rules had non-zero entries (highlighted yellow). So I want to count '2' - i.e. two rules were flagged during the period under review (as compared to 3, as the same rule flagged in consecutive months, but i only want to count the rules that have been broken, not the number of times the same rule has been broken).
Can anyone help? I had initially thought combining index match with countif would do the job, but I don't think i'm anywhere near close with that option.
Many thanks,
Steph
I'm a bit stumped on how to solve a particular data problem and was hoping for a nod in the right direction.
I have a table, which along the top going across columns has some unique codes in row 1, and some categories attached to those codes in row 2, which are populated using a vlookup. The number of rules/columns across the top varies, although I can fix the formula to go to from A-Z. Each unique code can have the same category attached to it as another code in row 2.
I have dates going down the table in column A.
Within the table, I have some numbers which are populated using countifs based on the code in row 1 and the date in column A. Essentially, they are counting the number of times a warning against a particular code is flagged in a separate data sheet in a given month.
I need to write a formula, that counts if there has been any 'non-zero' entries in the data table, associated with a particular category, between two dates.
For example:
I want to count the number of rules that have been broken relating to the 'IMA' category, between 31/01/2016 and 29/02/2016:
So in the above, there are two rules with the category 'IMA' in row 2. Between the dates of Jan-Feb 2016 (column A), both of these rules had non-zero entries (highlighted yellow). So I want to count '2' - i.e. two rules were flagged during the period under review (as compared to 3, as the same rule flagged in consecutive months, but i only want to count the rules that have been broken, not the number of times the same rule has been broken).
Can anyone help? I had initially thought combining index match with countif would do the job, but I don't think i'm anywhere near close with that option.
Many thanks,
Steph
