COUNTIFS using an array, but a continual -1 reference
 

I have the current working formula:

{=COUNTIFS(TRIP!$A$2:TRIP!$A$65536,D$16,TRIP!$B$2: TRIP!$B$65536,"<5")}

This works tremendously, however I need to add one more restriction and
can't figure out how to include it. I need to add a:
TRIP!$C$2:TRIP!$C$65536,TRIP!C3<TRIP!C2

To further specify, here is an example:
TRIP!A TRIP!B TRIP!C D
1 3 4598
1
1 4 1578
1
1 0 1579
1
1 8 3568
1
1 4 8585
1

In this scenario I want to count 4 trips. Trip 1, 2, 3, and 5. My current
formula counts three because it dismisses trip 3 due to the zero value. If I
can put the < expression in the formula I will eliminate the 0 expression
(as the 0 would automatically dismiss that record anyhow... currently it's
the closest expression I've found to what I actually want).

Thank you!!

COUNTIFS using an array, but a continual -1 reference
 

{=COUNTIFS(TRIP!$A$2:TRIP!$A$65536,D$16,TRIP!$B$2 :TRIP!$B$65536,"<5")}

That formula doesn't need to be array entered. Also, you don't need to
repeat the sheet name in your references.

Normally entered:

=COUNTIFS(TRIP!$A$2:$A$65536,D$16,TRIP!$B$2:$B$655 36,"<5")

Not sure what you're trying to say with this:

I need to add a:
TRIP!$C$2:TRIP!$C$65536,TRIP!C3<TRIP!C2

That would look something like this *but* it won't work:

TRIP!$C$3:$C$65536<TRIP!$C$2:TRIP!$C$65535

If you want to exclude 0 from the count:

=COUNTIFS(TRIP!$A$2:$A$65536,D$16,TRIP!$B$2:$B$655 36,"0",TRIP!$B$2:$B$65536,"<5")

--
Biff
Microsoft Excel MVP


"Anthony" wrote in message
...
I have the current working formula:

{=COUNTIFS(TRIP!$A$2:TRIP!$A$65536,D$16,TRIP!$B$2: TRIP!$B$65536,"<5")}

This works tremendously, however I need to add one more restriction and
can't figure out how to include it. I need to add a:
TRIP!$C$2:TRIP!$C$65536,TRIP!C3<TRIP!C2

To further specify, here is an example:
TRIP!A TRIP!B TRIP!C D
1 3 4598
1
1 4 1578
1
1 0 1579
1
1 8 3568
1
1 4 8585
1

In this scenario I want to count 4 trips. Trip 1, 2, 3, and 5. My
current
formula counts three because it dismisses trip 3 due to the zero value.
If I
can put the < expression in the formula I will eliminate the 0
expression
(as the 0 would automatically dismiss that record anyhow... currently
it's
the closest expression I've found to what I actually want).

Thank you!!