CountIfs in Excel

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?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
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.

ProfessorJimJamCommented:
Would be much easy to have the sample file uploaded
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Here's a sample file. So I would need a count of all the different order #'s in column A. Where the date in column B is between 01/01/11 and 01/04/11, where the value in column D is BLANKET, and where the value in column L is 1
SampleFile.xlsx
0
Saqib Husain, SyedEngineerCommented:
You can use a pivot table to do this.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'd rather not use a pivot table to be honest.
0
ProfessorJimJamCommented:
here is the solution with formula. it is an array formula requires Control Shift Enter

=SUM(IF(FREQUENCY(IF($A$2:$A$46<>"",IF(AND($B$2:$B$46>=N2,$B$2:$B$46<=O2),IF($L$2:$L$46=P2,IF($D$2:$D$46=Q2,MATCH($A$2:$A$46,$A$2:$A$46,0))))),ROW($A$2:$A$46)-ROW($A$2)+1),1))

plz see attached.

by the way,

you can hardcode the criteria into the formula instead of having it on cells
SampleFile.xlsx
0
Saurabh Singh TeotiaCommented:
Perhaps you can even use this formula...

=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))

Open in new window


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
0
ProfessorJimJamCommented:
Saurabh,

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.
0
ProfessorJimJamCommented:
Saurabh,  i hear romurs that you are one of the most fastest responder in EE. :-)
0
Saurabh Singh TeotiaCommented:
Jim,

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...
0
ProfessorJimJamCommented:
yeah, i cannot even imagine competing with barry, i have learned alot from him in mrexcel msgboard. he is very knowlegable person in formulas , especially when it comes with date functions.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ok so I'm using your formula but I'm not getting any results. I have a summary tab that has the formula on it. And a Detail tab that has all the detail like the example I posted. Can you see anything wrong with my formula?


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

Open in new window

0
ProfessorJimJamCommented:
In the formula attached two things

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 .
0
Saurabh Singh TeotiaCommented:
Lawrence Salvucci,

Did you check the file that i uploaded for the formula??

Saurabh...
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Jim,
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.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Saurabh,
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.
0
Saurabh Singh TeotiaCommented:
Lawrence,

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...
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'm open to another solution using VBA. I would greatly appreciate the help!
0
Saurabh Singh TeotiaCommented:
Lawrence Salvucci,

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...
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
There could potentially be 1 more criteria. It would be column E (Region). I don't believe there would be anymore than that.
0
ProfessorJimJamCommented:
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
0
Saurabh Singh TeotiaCommented:
Jim,

Can you message me the link..I will definitely want to have a look over it...:-)

Saurabh...
0
Saurabh Singh TeotiaCommented:
Lawrance,

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

Open in new window


Saurabh...
SampleFile-1-2.xlsm
0

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
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much! That VBA code worked like a charm! And it was very quick too! I really appreciate all the help!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.