software

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 quot;RONquot; 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 quot;Isdquot;,(LGW,BGI) These would be in Column A amp; 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 SFOAssume the headers in A:E are called Header1 - Header5, the table is called
MyTable, the criteria range is F1:J2
=DCOUNT(MyTable,quot;Header4quot;,F1:J2)

F1:H2 would look like

Header1Header2Header3Header4Header5
lt;gt;lsdlt;gt;Ronlt;gt;Ron319lt;gt;lsdanother way would be

=SUMPRODUCT(--(A5:A500lt;gt;quot;lsdquot;),--(B5:B500lt;gt;quot;Ronquot;),--(C5:C500lt;gt;quot;Ronquot;),--(D5500=319),--(E5:E500lt;gt;quot;lsdquot;))

I am sure DCOUNT is faster if the table is big

Regards,Peo Sjoblom
quot;cltjohnquot; wrote:

gt; I'm having a problem getting the right formula to give me a count of column D
gt; by each type. An example would be to get the total number of 319's in column
gt; D with the following criteria. Do not count if quot;RONquot; is in B or C column
gt; plus I have a lookup for certain cities that I don't want to be in the count
gt; called quot;Isdquot;,(LGW,BGI) These would be in Column A amp; E. I've been trying
gt; Dcount but having a problem.
gt; Any help would greatly be appreaciated.
gt;
gt; Thx John
gt;
gt; Totals
gt; A B C D E 319 320
gt; 321 7W5
gt; 1 LGW 1141 1141 319 BUF 1 2 0
gt; 0
gt; 2 MCI 1439 458 320 BOS
gt; 3 PHL 121 RON 320 LGA
gt; 4 BGI 1130 1130 321 CLT
gt; 5 RIC 567 567 7W5 LGW
gt; 6 CLT 1920 1920 319 DTW
gt; 7 BNA RON 60 319 GSO
gt; 8 LAX 592 592 320 SFO
gt;

Thanks for the help.

quot;Peo Sjoblomquot; wrote:

gt; Assume the headers in A:E are called Header1 - Header5, the table is called
gt; MyTable, the criteria range is F1:J2
gt;
gt;
gt;
gt; =DCOUNT(MyTable,quot;Header4quot;,F1:J2)
gt;
gt; F1:H2 would look like
gt;
gt; Header1Header2Header3Header4Header5
gt; lt;gt;lsdlt;gt;Ronlt;gt;Ron319lt;gt;lsd
gt;
gt;
gt; another way would be
gt;
gt; =SUMPRODUCT(--(A5:A500lt;gt;quot;lsdquot;),--(B5:B500lt;gt;quot;Ronquot;),--(C5:C500lt;gt;quot;Ronquot;),--(D5500=319),--(E5:E500lt;gt;quot;lsdquot;))
gt;
gt; I am sure DCOUNT is faster if the table is big
gt;
gt; Regards,
gt;
gt;
gt; Peo Sjoblom
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;cltjohnquot; wrote:
gt;
gt; gt; I'm having a problem getting the right formula to give me a count of column D
gt; gt; by each type. An example would be to get the total number of 319's in column
gt; gt; D with the following criteria. Do not count if quot;RONquot; is in B or C column
gt; gt; plus I have a lookup for certain cities that I don't want to be in the count
gt; gt; called quot;Isdquot;,(LGW,BGI) These would be in Column A amp; E. I've been trying
gt; gt; Dcount but having a problem.
gt; gt; Any help would greatly be appreaciated.
gt; gt;
gt; gt; Thx John
gt; gt;
gt; gt; Totals
gt; gt; A B C D E 319 320
gt; gt; 321 7W5
gt; gt; 1 LGW 1141 1141 319 BUF 1 2 0
gt; gt; 0
gt; gt; 2 MCI 1439 458 320 BOS
gt; gt; 3 PHL 121 RON 320 LGA
gt; gt; 4 BGI 1130 1130 321 CLT
gt; gt; 5 RIC 567 567 7W5 LGW
gt; gt; 6 CLT 1920 1920 319 DTW
gt; gt; 7 BNA RON 60 319 GSO
gt; gt; 8 LAX 592 592 320 SFO
gt; gt;