|
Count
Looking for a formula where, using the data below, if I pick a city, such as
PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
I think I would go for a Pivot Table. Takes a few experiments the first time
to get it right but it is a very powerful tool to know how to use. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mark O" <Mark wrote in message ... Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
I quite familiar with Pivot tables, and it would work, but I am hoping to
create a drop down list for the city, which would allow a simple user to just change the dropdown to find the information. So really would just like a single formula i can put in a cell next to the drop down. "Bernard Liengme" wrote: I think I would go for a Pivot Table. Takes a few experiments the first time to get it right but it is a very powerful tool to know how to use. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mark O" <Mark wrote in message ... Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
=COUNTIF(B:B,C1)
where C1 is the dropdown box. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mark O" wrote in message ... I quite familiar with Pivot tables, and it would work, but I am hoping to create a drop down list for the city, which would allow a simple user to just change the dropdown to find the information. So really would just like a single formula i can put in a cell next to the drop down. "Bernard Liengme" wrote: I think I would go for a Pivot Table. Takes a few experiments the first time to get it right but it is a very powerful tool to know how to use. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mark O" <Mark wrote in message ... Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
Assuming your data is in column a and b
Use =countif(b1:b8,"Phoenix) in column b. "Mark O" wrote: Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
Bob, that won't quite work. I have an extra element to this you are missing.
Your example would work if I was counting occurences of Phoenix only. There are two columns here. One with the city and one with vendors. I am then trying to count how many unique vendors are in Phoenix (or which ever city I choose in the drop down). "Bob Phillips" wrote: =COUNTIF(B:B,C1) where C1 is the dropdown box. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mark O" wrote in message ... I quite familiar with Pivot tables, and it would work, but I am hoping to create a drop down list for the city, which would allow a simple user to just change the dropdown to find the information. So really would just like a single formula i can put in a cell next to the drop down. "Bernard Liengme" wrote: I think I would go for a Pivot Table. Takes a few experiments the first time to get it right but it is a very powerful tool to know how to use. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mark O" <Mark wrote in message ... Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
I have an extra element to this you are missing. Your example would work if
I was counting occurences of Phoenix only. There are two columns here. One with the vendors and one with the city. I am trying to count how many unique vendors are in Phoenix (or which ever city I choose in the drop down). "E10" wrote: Assuming your data is in column a and b Use =countif(b1:b8,"Phoenix) in column b. "Mark O" wrote: Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
Assuming C1 is your "dropdown" and A2:B25 your table, try this ARRAY FORMULA
(Commit with Ctrl-Shift-Enter): =COUNT( 1 / ( MATCH( $A$2:$A$25, IF( $B$2:$B$25 = $C$1, $A$2:$A$25, #N/A ), 0 ) = ( ROW( $A$2:$A$25 ) - ROW( $A$2 ) + 1 ) ) ) Adjust to your ranges... -- Regards, Luc. "Festina Lente" "Mark O" wrote: Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
Count
=SUM(--(FREQUENCY(IF(B2:B100=C1,MATCH(A2:A100,A2:A100,0)) ,ROW(INDIRECT("1:"&ROWS(A2:A100))))0))
This is an array formula, so commit with Ctrl-Shift-Enter, and still assumes the dropdown in C1. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mark O" wrote in message ... Bob, that won't quite work. I have an extra element to this you are missing. Your example would work if I was counting occurences of Phoenix only. There are two columns here. One with the city and one with vendors. I am then trying to count how many unique vendors are in Phoenix (or which ever city I choose in the drop down). "Bob Phillips" wrote: =COUNTIF(B:B,C1) where C1 is the dropdown box. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mark O" wrote in message ... I quite familiar with Pivot tables, and it would work, but I am hoping to create a drop down list for the city, which would allow a simple user to just change the dropdown to find the information. So really would just like a single formula i can put in a cell next to the drop down. "Bernard Liengme" wrote: I think I would go for a Pivot Table. Takes a few experiments the first time to get it right but it is a very powerful tool to know how to use. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Mark O" <Mark wrote in message ... Looking for a formula where, using the data below, if I pick a city, such as PHOENIX, it will count the number of unique vendors that city has. So this example would give me the value of 4 (unique vendors A, B, C, and D). Vendor City A PHOENIX B PHOENIX B PHOENIX C PHOENIX C PHOENIX D PHOENIX A LA C CHICAGO |
| All times are GMT +1. The time now is 05:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com