Countif offset formula comparing 2 values in same column

0
votes

Need help with a formula that will count a value only if it's 3 rows below a different value. Both values are in Column c. Example. "Value1" is listed 5 times. So I have a countif for a total of 5. 2 times it is 3 rows below "value2". I want a formula that will count "value1" only if it's 3 rows below "value2". So my formula would generate "2". I have a offset countif but I cannot get it to condition based on "value2"

1
excelexcel-formulacountif
Please show some test data and expected outcome, it is easier to understand than simple text explanation. - Scott Craner

1 Answers

0
votes

You can stagger (i.e. offset) the various criteria_range(s) of a COUNTIFS function. It is important that the ranges are the same size and shape; it not important that they are exactly the same rows or columns.

=COUNTIFS(C1:C99, "value2", C4:C102, "value1")

Note that C4:C102 (criteria_range2 for value1) is still 99 rows but offset 3 rows from C1:C99 (criteria_range1 for value2).

    countifs_stagger