DCOUNT WITH MULTIPLE CRITERIAS

[Guest]

I'm having a problem getting the right formula to give me a count of column D
by each type. An example would be to get the total number of 319's in column
D with the following criteria. Do not count if "RON" is in B or C column
plus I have a lookup for certain cities that I don't want to be in the count
called "Isd",(LGW,BGI) These would be in Column A & E. I've been trying
Dcount but having a problem.
Any help would greatly be appreaciated.

Thx John

Totals
A B C D E 319 320
321 7W5
1 LGW 1141 1141 319 BUF 1 2 0
0
2 MCI 1439 458 320 BOS
3 PHL 121 RON 320 LGA
4 BGI 1130 1130 321 CLT
5 RIC 567 567 7W5 LGW
6 CLT 1920 1920 319 DTW
7 BNA RON 60 319 GSO
8 LAX 592 592 320 SFO

 

[Guest]

Assume the headers in A:E are called Header1 - Header5, the table is called
MyTable, the criteria range is F1:J2



=DCOUNT(MyTable,"Header4",F1:J2)

F1:H2 would look like

Header1 Header2 Header3 Header4 Header5
<>lsd <>Ron <>Ron 319 <>lsd


another way would be

=SUMPRODUCT(--(A5:A500<>"lsd"),--(B5:B500<>"Ron"),--(C5:C500<>"Ron"),--(D5500=319),--(E5:E500<>"lsd"))

I am sure DCOUNT is faster if the table is big

Regards,


Peo Sjoblom

 

[Guest]

Thanks for the help.

Assume the headers in A:E are called Header1 - Header5, the table is called
MyTable, the criteria range is F1:J2



=DCOUNT(MyTable,"Header4",F1:J2)

F1:H2 would look like

Header1 Header2 Header3 Header4 Header5
<>lsd <>Ron <>Ron 319 <>lsd


another way would be

=SUMPRODUCT(--(A5:A500<>"lsd"),--(B5:B500<>"Ron"),--(C5:C500<>"Ron"),--(D5500=319),--(E5:E500<>"lsd"))

I am sure DCOUNT is faster if the table is big

Regards,


Peo Sjoblom