DCOUNT WITH MULTIPLE CRITERIAS

[cltjohn]

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

[Peo Sjoblom]

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
>

[cltjohn]

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