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

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"),--(D5:D500=319),--(E5:E500<"lsd"))

I am sure DCOUNT is faster if the table is big

Regards,


Peo Sjoblom






"cltjohn" wrote:

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

Thanks for the help.

"Peo Sjoblom" wrote:

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"),--(D5:D500=319),--(E5:E500<"lsd"))

I am sure DCOUNT is faster if the table is big

Regards,


Peo Sjoblom






"cltjohn" wrote:

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