Hello,
See picture below:
COUNTIFS.png
I wish to create a COUNTIFS formula that functions as follows:
a. Counts how many times values in Column D & E reappear.
b. Resets to 0 if Criteria X changes. (Changed marked with yellow.)
c. Resets to 1 if Criteria Y changes. (Changes marked with blue.)
d. Resets to 0 if both criteria changes.
Multiple Name+Thing combinations with their own criteria X and Y exist. This is why this must be included in the formula's criteria. I've hidden some sample rows with Anne+Shirt instead of Bob+Car as an example of this. Bob+Thing is independent of Anne+Shirt.
Additional info:
Criteria X goes from 0 to ∞.
Criteria Y only goes from 0 to 1.
All changes in Criteria X and Y are in consecutive increasing increments of 1. I.e. 1, 2, 3, 4, 5, etc...
I've been able to make the formula work for all points except that for point (c.), where it should reset to 1 if Criteria Y changes with:
In N3: =COUNTIFS($D$3:D3;D3; $E$3:E3;E3; $F$3:F3;F3; $G$3:G3;G3)-IF(F3>0;1;0)
(The IF(F3>0;1;0) is to make the formula reset to 0, and start at 1 at N3 (since no changes happen at N3, like 0 to 1, 1 to 2, etc..).
See attached Excel sheet.
It's easier to understand when comparing the source data to a visual representation of the desired end result.
Thanks
EDIT: See last post by AliGW on page 2 for the solution.
Bookmarks