# circular reference

#### breilly00

##### New Member
I am searching a sheet named data25 for certain values and counting the number of times they occur
A sample of some rows in sheet data25 is below
 Date/Time First Detected Speed Last Detected Speed 2/1/2021 0:37​ 64​ 50​ 2/1/2021 4:10​ 37​ 28​ 2/1/2021 4:11​ 19​ 26​ 2/1/2021 5:29​ 14​ 25​ 2/1/2021 5:37​ 36​ 33​ 2/1/2021 5:44​ 52​ 46​
In my results sheet I have the following starting at row 5. I am searching the first detected and last detected to see if either of the cells should be counted for the range of Low MPH or High MPH. In the count cell, I have this formula...=COUNTIFS(data25!\$B\$2:data25!\$C\$35000,">="&A6,data25!\$B\$2:data25!\$C\$35000,"<="&B6)-SUMPRODUCT(--(data25!\$B\$2:data25!\$B\$35000>=A6),--(data25!\$B\$2:data25!\$B\$35000<=B6),--(data25!\$B\$2:data25!\$B\$35000=\$C\$2:\$C\$35000))
and I get a circular reference and a value of 0 in the cell.

What do I have to change?

 L-MPH H-MPH COUNT 25​ 34​ 0​ 35​ 44​ 45​ 54​ 55​ 64​ 65​ 74​ 75​ 84​ 85​ 94​ 95​ 104​ 105​ 114​ 115​ 124​ 125​ 134​

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Fluff

##### MrExcel MVP, Moderator
If your trying to get a count, try
Excel Formula:
``=COUNTIFS(data25!\$B\$2:data25!\$C\$35000,">="&A6,data25!\$B\$2:data25!\$C\$35000,"<="&B6)``

#### breilly00

##### New Member
sending porkchops and beer. Works like a charm.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
197
Replies
7
Views
321
Replies
2
Views
98
Replies
12
Views
123
Replies
12
Views
275

1,141,479
Messages
5,706,621
Members
421,460
Latest member
Taamrak

### 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.

### Which adblocker are you using?

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

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