Sequential Count with 2 Criteria

[JO505]

In the sample I need to produce a sequential count for columns J & K. As the year is changed I want the count in each column to reflect the change.

Jim O

[teylyn]

There are circular references in your spreadsheet. The Countif is referring to its own cell.

Instead of a formula that is not working, can you post what the desired result is? Currently I see all 0s with a few 1 sprinkled in. What do you expect instead? What is the logic?

[JO505]

I added 2 helper columns and changed the formulas. I was hoping to do this without the helper columns but if their is no other way I can live with them.

Jim O

[David A Coop]

Hello JO505,

I was confused by your second post - what I think you wanted was this: =IF(AND($E2=$I$1,F2=$J$1),COUNTIF(J$1:J1,">0")+1,0)
and =IF(AND($E2=$I$1,F2=$K$1),COUNTIF(K$1:K1,">0")+1,0)

Please advise if I can ignore the second post.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[teylyn]

I arrived at the same formula as David A Coop. When the condition is met you just need to count all the values above the current cell that are greater than 0 and the add 1 to increment. That way you can avoid a circular reference and don't need a helper column.

[JO505]

That is what I was looking for. Thank you both for your time and input.

Jim O

[David A Coop]

That is what I was looking for. Thank you both for your time and input.

Jim O

You're welcome Jim. Please remember to mark the post as SOLVED, and click on the Rep star.

DAC