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

ErikvM

New Member
Joined
Sep 15, 2014
Messages
3
I have a sheet where I have the following data:

IDDate
3414141073200
3414141073941
3501141077892
3508141077999
3509141078011
3509141078191
3580141078192

<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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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))}
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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