COUNTIF between two values

Lux Aeterna

Board Regular
Joined
Aug 27, 2015
Messages
205
Office Version
  1. 2019
Platform
  1. 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 :/
1688130348478.png


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.
1688130219908.png


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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Surely a COUNTIFS function formula would do the trick?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.

Excel Formula:
=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)))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top