formula to give sample number of a certain sample type and on a certain day

[Larsvane]

Good afternoon,

I need some help with a formula I want to use.

I am trying to put together a formula that gives me a number if some conditions are met. The example excel is as follows:

Productsample	Date	Time	Number of samples per day
Wheat	12-april-23	6:00
Wheat	12-april-23	7:00
Rye	12-april-23	8:00
Wheat	12-april-23	9:00
Wheat	13-april-23	6:00

I want a formula in the Number of samples per day column. It should check if the productsample is Wheat or Rye. Then if it is the first sample of that day or if it is the 2nd or third, etc. So the output I want to be in column D in this case would be: 

Productsample	Date	Time	Number of samples per day
Wheat	12-april-23	6:00	1
Wheat	12-april-23	7:00	2
Rye	12-april-23	8:00	1
Wheat	12-april-23	9:00	3
Wheat	13-april-23	6:00	1

Can someone tell me if this is possible? Preferably without macro's

Many thanks in advance!

[#2]

@Larsvane 

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)

This formula returns the intended result in my sheet.

[#3]

@Larsvane 

 It is possible to achieve the desired result without using macros.

You can use the COUNTIFS function in Excel to count the number of samples for each product on a given day.

Here’s an example formula that you can use in cell D2 and then drag down to fill the rest of the column:

=COUNTIFS(A$2:A2,A2,B$2:B2,B2)

This formula counts the number of rows in column A (Productsample) that match the current row’s product and have a date in column B (Date) that matches the current row’s date.

The result is the number of samples for that product on that day up to and including the current row.

See the file!

I hope this helps!