Forum Discussion
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
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!
