countif multiple criteria

[breilly00]

need a formula to count number of times a value is between 2 ranges. But if the range numbers are equal then do not count

data in sheet data25 looks like this

Date/Time (col A)	First Detected Speed (Col B)	Last Detected Speed (Col C)
4/1/2021 0:40​	35​	35​
4/1/2021 0:41​	40​	10​
4/1/2021 0:42​	44​	10​
4/1/2021 0:43​	10​	44​

I am creating a graph of the number of times that speed of car was between 2 values.
For example:
My graph data would look like this
Speed count
35-45 ####

In this case the count should be 4. because the either the first detected speed or the last detected speed of between 35-45 occurred 4 times. 3 4. I am using the following formula..."=COUNTIFS(data25!B:B,">34",data25!B:B,"<45")+COUNTIFS(data25!C:C,">34",data25!C:C,"<45")". However, this formula produces a count of 5 because it is counting that someone was doing the same speed as recorded in the first detected and last detected.

How can I modify the formula to check if the 2 speeds are the same then only count once and not twice.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1	Date/Time	First Detected Speed	Last Detected Speed		Speed Count
2	4/1/2021 0:40	35	35		Low	High	Count
3	4/1/2021 0:41	40	10		35	45	4
4	4/1/2021 0:42	44	10
5	4/1/2021 0:43	10	44
Sheet12

Cell Formulas
Range	Formula
G3	G3	=COUNTIFS(B2:C100,">="&E3,B2:C100,"<="&F3)-SUMPRODUCT(--(B2:B100>=E3),--(B2:B100<=F3),--(B2:B100=C2:C100))

 

[breilly00]

Eric:

Just what I needed. Put the formula in my test file and got it to work with my layouts. And yet, our actual data will be dynamic each month and quite large. For example for April of this year we will have about 22,000 rows. I tried to just change the b2:c100 to B:C so the formulas would pick up all the data in the columns. But Excel threw me an error (#error). What could I do to change your formula so it looks at all our rows in a worksheet. Also, am not sure how to get around the headers being their. Or could I just hard code something like 50,000 rows. This would be a number that we would not ever reach but I am sure that 'hard coding' this is not a best practice.

 

[Eric W]

Actually, hardcoding a large row value is an accepted way to handle something like this. Using whole column references in COUNTIFS is ok, since COUNTIFS (and the other "*IF" functions) "knows" where the end of data is, so it only goes as far as needed. It also does not generate errors with non-numeric data. SUMPRODUCT does not have either of those benefits, so you need to just code it to find the end of your data. Using whole column references in SUMPRODUCT would require it to examine all 1,000,000+ rows, which can be very slow.

There are a couple ways to set the last row of a column. The first is what you suggested, pick a row that is beyond whatever you think your data will reach. Another way is to use a Name for your ranges, and use a formula to find the last row. Like this:

Book1
	A	B	C	D	E	F	G
1	Date/Time	First Detected Speed	Last Detected Speed		Speed Count
2	4/1/21 0:40	35	35		Low	High	Count
3	4/1/21 0:41	40	10		35	45	4
4	4/1/21 0:42	44	10
5	4/1/21 0:43	10	44
6
Sheet15

Cell Formulas
Range	Formula
G3	G3	=COUNTIFS(B:C,">="&E3,B:C,"<="&F3)-SUMPRODUCT(--(First>=E3),--(First<=F3),--(First=Last))

Named Ranges
Name	Refers To	Cells
First	=Sheet15!$B$2:INDEX(Sheet15!$B:$B,LOOKUP(2^999,Sheet15!$B:$B,ROW(Sheet15!$B:$B)))	G3
Last	=Sheet15!$C$2:INDEX(Sheet15!$C:$C,LOOKUP(2^999,Sheet15!$C:$C,ROW(Sheet15!$C:$C)))	G3

 

[breilly00]

Thank you so much for the great information. I'm going to go with the option of putting a number in that will exceed our expectation of traffic volume.
Mr. Excel has super and wonderful support that is only provided by the talent and commitment of individuals like yourself. Wish I could take you downtown to the downunder club for a cold one.

Thank you for your help. Bruce