COUNTIFS using an array, but a continual -1 reference

A

Anthony

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

T. Valko

{=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$65536,"<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$65536,">0",TRIP!$B$2:$B$65536,"<5")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top