New Contributor

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

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 Replies

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

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

This formula returns the intended result in my sheet.

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

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!