• 411K Members
• 6,662 Online
• 466K Conversations

New Contributor

When a cell value changes to "Yes" no matter how many times, increment the next cell value by 1

Hi Community,

I want help on following:

Problem

I would like to do, when A2 changes to "Yes" everytime, B2 increases by 1. But the hassle for me here is, If A2 changes back to "No", B2 should remain 1, and when A2 again changes back to "Yes" the second time, B2 becomes 2 (increment by 1). so on and on..

Context: Why I want this?

• A customer 123@gmail.com subscribed to newsletter, hence 123@gmail.com is subscribed ("Yes") and the subscribed time is 1.
• The same customer now unsubscribes from newsletter, hence 123@gmail.com is unsubscribed ("No") but since he had previously subscribed once, the subscribed time is 1.
• The same customer now again subscribes to newsletter for the second time, hence 123@gmail.com is subscribed ("Yes")  and the subscribed time is 2.
• And so on .. if unsubscribe again, times remain same 2, if subscribed again for third time, times changes to 3

I hope it makes sense.

Thank you

2 Replies

Re: When a cell value changes to "Yes" no matter how many times, increment the next cell v

As variant you may have a list with all subcribings/unsubsribings and COUNTIFS how many times the account subscribed.

Re: When a cell value changes to "Yes" no matter how many times, increment the next cell v

I suggest that your Column Labels in A1:C1 must be Subscriber, Subscribed, and Instance. Then your formula in C2, copied down the rows, is:
=COUNTIFS(A\$2:A2,A2,
B\$2:B2,"Yes")
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies