If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!C3TRIP!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!! |
#2
|
|||
|
|||
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!C3TRIP!C2 That would look something like this *but* it won't work: TRIP!$C$3:$C$65536TRIP!$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!C3TRIP!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!! |
Thread Tools | |
Display Modes | |
|
|