Lux Aeterna
Board Regular
- Joined
- Aug 27, 2015
- Messages
- 205
- Office Version
- 2019
- Platform
- Windows
Hello once again
There's an excel sheet that we keep our monthly stats. I would like to be able to count between two values.
I'll try to explain through images!
This is our annual list. Please note that ID 1 is in cell A5. For a similar project, we had to use the SUMPRODUCT function, which I have no idea what it does :D (e.g. cell Q5) - Can't find the related post, sorry :/
This is the stats section of the sheet. The number in brown is the last ID of each month. The number next to it is the total number of samples we examined that month.
At the end of each month, we write our last ID and it calculates the total number of samples for that month.
What I'd like is a COUNTIF function as follows;
For January, between 1 and N5_value (in this picture, between 1 and 3)
For February, between N5_value+1 and N6_value (in this picture, between 4 and 7)
For March, between N6_value+1 and N7_value (in this picture, between 8 and 9)
and so on for the next months
It should count a, b, c, d in the B column (B5 to B10033), so I suppose I need 4 different functions (I'll adjust the rest 3 on the one you provide).
The final outcome should be something like
January, a; 2
b; 1
c; 0
d; 0
February, a; 1
b; 1
c; 1
d; 1
etc.
Please note that the per month stats will be in a different sheet and we need to reference the sheet that it will draw data from using this formula
.
Hope my message makes sense and thank you once again in advance
PS: If necessary, you can find the workbook here.
Workbook password is 299
Stats sheet password is 24823
There's an excel sheet that we keep our monthly stats. I would like to be able to count between two values.
I'll try to explain through images!
This is our annual list. Please note that ID 1 is in cell A5. For a similar project, we had to use the SUMPRODUCT function, which I have no idea what it does :D (e.g. cell Q5) - Can't find the related post, sorry :/
This is the stats section of the sheet. The number in brown is the last ID of each month. The number next to it is the total number of samples we examined that month.
At the end of each month, we write our last ID and it calculates the total number of samples for that month.
What I'd like is a COUNTIF function as follows;
For January, between 1 and N5_value (in this picture, between 1 and 3)
For February, between N5_value+1 and N6_value (in this picture, between 4 and 7)
For March, between N6_value+1 and N7_value (in this picture, between 8 and 9)
and so on for the next months
It should count a, b, c, d in the B column (B5 to B10033), so I suppose I need 4 different functions (I'll adjust the rest 3 on the one you provide).
The final outcome should be something like
January, a; 2
b; 1
c; 0
d; 0
February, a; 1
b; 1
c; 1
d; 1
etc.
Please note that the per month stats will be in a different sheet and we need to reference the sheet that it will draw data from using this formula
VBA Code:
INDIRECT("List"&B$2&"!$B$5:$B$10033")
Hope my message makes sense and thank you once again in advance
PS: If necessary, you can find the workbook here.
Workbook password is 299
Stats sheet password is 24823