The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I'm trying to do a count of how many different order #'s there are in column A. But I also need to only count the # of different orders between 2 dates. The dates are in column B. Then I also need to add another criteria where the value in column D is a specific value and then another specific value in column L. Any ideas on how I would write this countif?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SampleFile.xlsx

=SUM(IF(FREQUENCY(IF($A$2:

plz see attached.

by the way,

you can hardcode the criteria into the formula instead of having it on cells

SampleFile.xlsx

```
=SUMPRODUCT(((A2:A46<>"")*(B2:B46>=N2)*(B2:B46<=O2)*(L2:L46=P2)*(TRIM(SUBSTITUTE(D2:D46,CHAR(160),""))=TRIM(Q2)))/COUNTIF(A2:A46,A2:A46))
```

Enclosed is the sample file of your reference...

Jim..You might want to check the file as well since the word K and M have special characters and if you type them with hand the formula fails..check my file for details..

Saurabh...

SampleFile-1.xlsx

the OP criteria was "BLANKET" so i only focused on that. i did not evaluate K and M, as my formula worked for "BLANKET" and is much faster in large data, than denominator on countif; however, i do like your approach with sumproduct trim and substitude it takes care of 160 char if exists and also does not require control shift enter. though, i provided solution faster than you :-) but hats off for your formula which beats mine.

Yeah sumproduct is an array formula in itself so you dont need to use array with it... And yeah when im back home...you will beat me most of the time as my kids will keep me busy.. ;-)

And it's Not a rumor it's truth..you should catch me sometime when i'm in action or sprint of solving... you will see.. :-)

I'm waiting for barry to post a more effective solution if their because you can't beat him when it come to formulas..he is formula's wizard... :-)

Saurabh...

```
SUM(IF(FREQUENCY(IF(Detail!$A$2:$A$52<>"",IF(AND(Detail!$B$2:$B$52>=Summary!$A9,Detail!$B$2:$B$52<=Summary!$B9),IF(Detail!$L$2:$L$52=Summary!$C$1,IF(Detail!$D$2:$D$52=Summary!$C$2,MATCH(Detail!$A$2:$A$52,Detail!$A$2:$A$52,0))))),ROW(Detail!$A$2:$A$52)-ROW(Detail!$A$2)+1),1))
```

Equal sign is missing at the beginning

Second when you enter formula , did you press it with control shift enter? Or just Enter? Because it wouldn't not work if these special keystrokes are not entered .

There's an equal sign at the beginning. I just didn't post it. And yes I did the ctrl-shift-enter combination as well.

I tried your formula as well and it works but it takes a long time to calculate since I have over 10,000 rows in total in my file. So when I entered your formula it took a bit to calculate. But yes yours worked once it did calculate. I have a few fields that will have this formula so I don't want it to take a long time to calculate them all.

Both the formulas will take equal amount of time since other formula is also an array formulas and they take time to calculate for sure..

If you are open then we can try writing a customized formula in VBA which can be faster but again depends upon your PC settings about how much time they take in the end to compare...

Saurabh...

Quick question before i design the vba solution..How many criterias you can have like in this case you have.. 4 Criteria which is start date and end date..Then on Usercode and on ProdCL.. Would have additional criteria's as well if yes then can you tell me how much max it will go to??

Saurabh...

use of frequency function for the unique data , is much faster than 1/division on Countif

you may test it with Charles FastExcel tool

Their you go i made UDF for you which does what you are looking for...

Depending upon your need you can add region to it or not.. The first parameter is the range where you want to check for unique values, then the column offset from main column since range will be 0 so you need to mention just the column number like mentioned in the enclosed file and first two inputs are for dates i.e. start and end date and rest inputs you can give whatever you feel like...

Once you apply this udf even on large data set you will see the difference in the speed..

```
Function getunique(r As Range, c1 As Long, r1 As Range, c2 As Long, r2 As Range, c3 As Long, r3 As Range, c4 As Long, r4 As Range, Optional c5 As Long, Optional r5 As Range)
Dim cell As Range
Dim col As Collection
Set col = New Collection
On Error Resume Next
For Each cell In r
If Not r5 Is Nothing Then
If cell.Offset(0, c1).Value >= r1.Value And cell.Offset(0, c2).Value <= r2.Value And UCase(Trim(Replace(cell.Offset(0, c3).Value, Chr(160), ""))) = UCase(Trim(r3.Value)) And UCase(Trim(Replace(cell.Offset(0, c4).Value, Chr(160), ""))) = UCase(Trim(r4.Value)) And UCase(Trim(Replace(cell.Offset(0, c5).Value, Chr(160), ""))) = UCase(Trim(r5.Value)) Then
col.Add cell.Value, CStr(cell.Value)
End If
Else
If cell.Offset(0, c1).Value >= r1.Value And cell.Offset(0, c2).Value <= r2.Value And UCase(Trim(Replace(cell.Offset(0, c3).Value, Chr(160), ""))) = UCase(Trim(r3.Value)) And UCase(Trim(Replace(cell.Offset(0, c4).Value, Chr(160), ""))) = UCase(Trim(r4.Value)) Then
col.Add Str$(cell.Value), Str$(cell.Value)
End If
End If
Next cell
getunique = col.Count
End Function
```

Saurabh...

SampleFile-1-2.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VBA

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.