Running Count of Occurrence in a List

CF64

New Member
Joined
Feb 17, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hello,
I did a search and while I found a thread about running a count of an occurrence it didn't exactly match what I am trying to do.
I am trying to set up a formula to perform a running count of an occurrence of a value in a list using more than one condition.
I have been trying to modify the following formula by adding an "AND"

Single condition: IF(a5="John",COUNTIF($a$5:a5,"John"),"")

My data is as follows:
Column A Column B Column C Column D
1 Seller Product Date
2 John Apple
3 Mary Orange
4 Tim Orange
5 John Apple 1/1/20
6 Bob Apple 1/1/20
7 Pete Orange 1/2/20
8 Mary Apple 1/3/20
9 John Apple 1/4/20

I would like Column D to return which occurrence of the seller's name we are seeing if the product = apple and there is a date in Column C

The formula I am using is: =IF(AND(A2=A2,C2<>"",B2="apple"),COUNTIF($A$2:A2,A2),"")
So, for seller John:
- in row 5, it returns a 2 but I would like it to return a 1
- in row 9, it returns a 3, but I would like it to return a 2

It seems to be counting row #2 for John even though the date column is blank

Any assistance you can provide is greatly appreciated.
Thank you
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,649
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(AND(A2="John",C2<>"",B2="apple"),COUNTIFS($A$2:A2,A2,B$2:B2,"Apple",C$2:C2,"<>"),"")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,649
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,143,687
Messages
5,720,296
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top