COUNTIF between two values

[Lux Aeterna]

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

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

 

[GlennUK]

Surely a COUNTIFS function formula would do the trick?

 

[Lux Aeterna]

Surely a COUNTIFS function formula would do the trick?

I think yes. I'd like to count a, b, c or d within a range. However, if you can think of another way to do it, I'm totally fine with it.

 

[GlennUK]

I think yes. I'd like to count a, b, c or d within a range. However, if you can think of another way to do it, I'm totally fine with it.

I don't understand .. I said to use COUNTIFS, and you kind of said yes, you'd like to do that .... but it sounds like you then did not try to do it. Or am I misunderstanding?

 

[Lux Aeterna]

I don't understand .. I said to use COUNTIFS, and you kind of said yes, you'd like to do that .... but it sounds like you then did not try to do it. Or am I misunderstanding?

I'll try to explain better.

COUNTIF (or COUNTIFS) is a possible option for me.

What I am not sure of is how to define the range within which the COUNTIF(S) function will work for each month.

Doesn't my first message help you understand at all?

 

[GlennUK]

To define a range of dates to count between in COUNTIFS, have a greater than (or equal to) start date as one condition, and less than (or equal to) end date as another condition. You can have more than 2 conditions, so can also specify the a, or b, or c, or d, too.

 

[Lux Aeterna]

That's a good option, but not feasible because some dates correspond to the following month. However I managed to find a workaround from a similar macro I used to use.

=SUMPRODUCT((INDIRECT("List"&B$2&"!$A$5:$A$10033")>INDIRECT("List"&B$2&"!$N$4"))*(INDIRECT("List"&B$2&"!$A$5:$A$10033")<=INDIRECT("List"&B$2&"!$N$5"))*(INDIRECT("List"&B$2&"!$B$5:$B$10033")=LEFT($A94;1)))