# 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
###### Who is Participating?

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

Microsoft Excel ExpertCommented:
Would be much easy to have the sample file uploaded
Information 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
EngineerCommented:
You can use a pivot table to do this.
Information Technology ManagerAuthor Commented:
I'd rather not use a pivot table to be honest.
Microsoft Excel ExpertCommented:
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
Commented:
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))
``````

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
Microsoft Excel ExpertCommented:
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.
Microsoft Excel ExpertCommented:
Saurabh,  i hear romurs that you are one of the most fastest responder in EE. :-)
Commented:
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...
Microsoft Excel ExpertCommented:
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.
Information 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))
``````
Microsoft Excel ExpertCommented:
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 .
Commented:
Lawrence Salvucci,

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

Saurabh...
Information 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.
Information 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.
Commented:
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...
Information Technology ManagerAuthor Commented:
I'm open to another solution using VBA. I would greatly appreciate the help!
Commented:
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...
Information 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.
Microsoft Excel ExpertCommented:
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
Commented:
Jim,

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

Saurabh...
Commented:
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

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
End If

End If
Next cell

getunique = col.Count
End Function
``````

Saurabh...
SampleFile-1-2.xlsm

Experts Exchange Solution brought to you by