# Count if criteria one more than 0 and criteria two in between

#### ErikvM

##### New Member
I have a sheet where I have the following data:

 ID Date 3414 141073200 3414 141073941 3501 141077892 3508 141077999 3509 141078011 3509 141078191 3580 141078192

<tbody>
</tbody>

I want to count how much unique id's are registered in a specific timeframe. To count the unique ID's I use =SUM(IF(FREQUENCY(A:A;A:A)>0;1;0)), which works.

But if then want to add the date inbetween i.e. 141073300 - 141078200 I get lost.
I found as result =SUM(IF(FREQUENCY(A:A;A:A)>0;(B:B>=D5)*(B:B<D37);0)) Where D5 and D37 refer to the cell that the value 141073300 and 141078200 are in.

But this doesn't work...

Does someone know what to do?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
The following array formula should help (you probably already know this, but array formulas are entered by holding down Ctrl+Shift, and hitting Enter, and that puts the curly brackets around the formula - no need to enter those manually), though there may be a better way to do it:

{=SUM(IF(FREQUENCY(IF((B2:B15>=E2)*(B2:B15<=F2)*A2:A15<>0,A2:A15,""),A2:A15),1,0))}

I had my "between" dates in E2 and F2, by the way.

Thanks! That Works, but not completely. The problem is in two things. First, my first row contains headers from data which is inserted through an external data source. It won't work when I also select the header. So if my function is A2:A80 it works, but A;A doesn't. Second, if I make it A2:A10000 it doesn't work anymore. Because then there are empty rows in my function and the result becomes 0.

So my function has to contain A:A, because the table varies in lenght due to the fact that the amount of data that comes in the table varies per hour, but it can't select the first row because then it doesn't work.

What should I change?

Your formula will need to refer to the same rows in all parts. So if you have A2:A10000, you need to make sure to have B2:B10000. Something like this:

{=SUM(IF(FREQUENCY(IF((B2:B10000>=E2)*(B2:B10000<=F2)*A2:A10000<>0,A2:A10000,""),A2:A10000),1,0))}

The other thing you could try is to create dynamic named ranges using the OFFSET function, and to use the named ranges (which will grown and shrink with your data) in the formula instead of specific cell references.

Thank you very, very much. Works like a charm!

Replies
1
Views
432
Replies
8
Views
910
Replies
5
Views
647
Replies
4
Views
438
Replies
0
Views
300

1,219,672
Messages
6,149,618
Members
450,904
Latest member
Gracifer

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