| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional count of unique values
Using v2003
I'll explain as clearly as possible... I need a formula that will do a count of unique values in a column based on the condition of two other columns. i.e. Col A is financial entity id and has duplicates: there may be 5 line items in entity 556 and 3 line items in entity 444, 5 line items 768, etc. Col B has the project impact: one of two values, either "Investment" or "ETC" Col C has the market ID: NE, SE, NW, SW I need a formula to return the count of unique financial entities that are Investment in the SE. For example, if the conditions apply to four of the five 556 entities, the formula would return "1". Thanks in advance for any guidance! Hope this is enough info. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional count of unique values
BTW, I've been trying to use an array formula that looks something like this,
but obviously with no luck... {=COUNT(IF($B$2:$B$1511="Investment",IF($C$2:$C$15 11="NE",SUM(IF(FREQUENCY(IF(LEN(A2:A1511)0,MATCH( A2:A1511,A2:A1511,0),""), IF(LEN(A2:A1511)0,MATCH(A2:A1511,A2:A1511,0),"")) 0,1)),0),0))} "Amy" wrote: Using v2003 I'll explain as clearly as possible... I need a formula that will do a count of unique values in a column based on the condition of two other columns. i.e. Col A is financial entity id and has duplicates: there may be 5 line items in entity 556 and 3 line items in entity 444, 5 line items 768, etc. Col B has the project impact: one of two values, either "Investment" or "ETC" Col C has the market ID: NE, SE, NW, SW I need a formula to return the count of unique financial entities that are Investment in the SE. For example, if the conditions apply to four of the five 556 entities, the formula would return "1". Thanks in advance for any guidance! Hope this is enough info. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional count of unique values
Try the below with your data in the below format. Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2 :C$100="SE",A$2:A$100)),A$2:A$100),1)) Col A Col B Col C 556 Investment NE 556 Investment SE 556 Investment NW 556 Investment SW 556 ETC NE 444 ETC SE 444 ETC NW 444 ETC SW 768 ETC NE 768 Investment SE 768 ETC NW 768 ETC SW 768 ETC NE If this post helps click Yes --------------- Jacob Skaria "Amy" wrote: BTW, I've been trying to use an array formula that looks something like this, but obviously with no luck... {=COUNT(IF($B$2:$B$1511="Investment",IF($C$2:$C$15 11="NE",SUM(IF(FREQUENCY(IF(LEN(A2:A1511)0,MATCH( A2:A1511,A2:A1511,0),""), IF(LEN(A2:A1511)0,MATCH(A2:A1511,A2:A1511,0),"")) 0,1)),0),0))} "Amy" wrote: Using v2003 I'll explain as clearly as possible... I need a formula that will do a count of unique values in a column based on the condition of two other columns. i.e. Col A is financial entity id and has duplicates: there may be 5 line items in entity 556 and 3 line items in entity 444, 5 line items 768, etc. Col B has the project impact: one of two values, either "Investment" or "ETC" Col C has the market ID: NE, SE, NW, SW I need a formula to return the count of unique financial entities that are Investment in the SE. For example, if the conditions apply to four of the five 556 entities, the formula would return "1". Thanks in advance for any guidance! Hope this is enough info. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional count of unique values
Mental head-slap for making life too complicated! Thanks!!
"Jacob Skaria" wrote: Try the below with your data in the below format. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2 :C$100="SE",A$2:A$100)),A$2:A$100),1)) Col A Col B Col C 556 Investment NE 556 Investment SE 556 Investment NW 556 Investment SW 556 ETC NE 444 ETC SE 444 ETC NW 444 ETC SW 768 ETC NE 768 Investment SE 768 ETC NW 768 ETC SW 768 ETC NE If this post helps click Yes --------------- Jacob Skaria "Amy" wrote: BTW, I've been trying to use an array formula that looks something like this, but obviously with no luck... {=COUNT(IF($B$2:$B$1511="Investment",IF($C$2:$C$15 11="NE",SUM(IF(FREQUENCY(IF(LEN(A2:A1511)0,MATCH( A2:A1511,A2:A1511,0),""), IF(LEN(A2:A1511)0,MATCH(A2:A1511,A2:A1511,0),"")) 0,1)),0),0))} "Amy" wrote: Using v2003 I'll explain as clearly as possible... I need a formula that will do a count of unique values in a column based on the condition of two other columns. i.e. Col A is financial entity id and has duplicates: there may be 5 line items in entity 556 and 3 line items in entity 444, 5 line items 768, etc. Col B has the project impact: one of two values, either "Investment" or "ETC" Col C has the market ID: NE, SE, NW, SW I need a formula to return the count of unique financial entities that are Investment in the SE. For example, if the conditions apply to four of the five 556 entities, the formula would return "1". Thanks in advance for any guidance! Hope this is enough info. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional count of unique values
Where would I insert another IF condition? Add column D to your example below
with Area ID in it, each cell with one of three values: Power, Antenna or Building Thanks! "Amy" wrote: Mental head-slap for making life too complicated! Thanks!! "Jacob Skaria" wrote: Try the below with your data in the below format. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2 :C$100="SE",A$2:A$100)),A$2:A$100),1)) Col A Col B Col C 556 Investment NE 556 Investment SE 556 Investment NW 556 Investment SW 556 ETC NE 444 ETC SE 444 ETC NW 444 ETC SW 768 ETC NE 768 Investment SE 768 ETC NW 768 ETC SW 768 ETC NE If this post helps click Yes --------------- Jacob Skaria "Amy" wrote: BTW, I've been trying to use an array formula that looks something like this, but obviously with no luck... {=COUNT(IF($B$2:$B$1511="Investment",IF($C$2:$C$15 11="NE",SUM(IF(FREQUENCY(IF(LEN(A2:A1511)0,MATCH( A2:A1511,A2:A1511,0),""), IF(LEN(A2:A1511)0,MATCH(A2:A1511,A2:A1511,0),"")) 0,1)),0),0))} "Amy" wrote: Using v2003 I'll explain as clearly as possible... I need a formula that will do a count of unique values in a column based on the condition of two other columns. i.e. Col A is financial entity id and has duplicates: there may be 5 line items in entity 556 and 3 line items in entity 444, 5 line items 768, etc. Col B has the project impact: one of two values, either "Investment" or "ETC" Col C has the market ID: NE, SE, NW, SW I need a formula to return the count of unique financial entities that are Investment in the SE. For example, if the conditions apply to four of the five 556 entities, the formula would return "1". Thanks in advance for any guidance! Hope this is enough info. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional count of unique values
Try
=SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2 :C$100="SE",IF(D$2:D$100="Power",A$2:A$100))),A$2: A$100),1)) If this post helps click Yes --------------- Jacob Skaria "Amy" wrote: Where would I insert another IF condition? Add column D to your example below with Area ID in it, each cell with one of three values: Power, Antenna or Building Thanks! "Amy" wrote: Mental head-slap for making life too complicated! Thanks!! "Jacob Skaria" wrote: Try the below with your data in the below format. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(FREQUENCY(IF(B$2:B$100="Investment",IF(C$2 :C$100="SE",A$2:A$100)),A$2:A$100),1)) Col A Col B Col C 556 Investment NE 556 Investment SE 556 Investment NW 556 Investment SW 556 ETC NE 444 ETC SE 444 ETC NW 444 ETC SW 768 ETC NE 768 Investment SE 768 ETC NW 768 ETC SW 768 ETC NE If this post helps click Yes --------------- Jacob Skaria "Amy" wrote: BTW, I've been trying to use an array formula that looks something like this, but obviously with no luck... {=COUNT(IF($B$2:$B$1511="Investment",IF($C$2:$C$15 11="NE",SUM(IF(FREQUENCY(IF(LEN(A2:A1511)0,MATCH( A2:A1511,A2:A1511,0),""), IF(LEN(A2:A1511)0,MATCH(A2:A1511,A2:A1511,0),"")) 0,1)),0),0))} "Amy" wrote: Using v2003 I'll explain as clearly as possible... I need a formula that will do a count of unique values in a column based on the condition of two other columns. i.e. Col A is financial entity id and has duplicates: there may be 5 line items in entity 556 and 3 line items in entity 444, 5 line items 768, etc. Col B has the project impact: one of two values, either "Investment" or "ETC" Col C has the market ID: NE, SE, NW, SW I need a formula to return the count of unique financial entities that are Investment in the SE. For example, if the conditions apply to four of the five 556 entities, the formula would return "1". Thanks in advance for any guidance! Hope this is enough info. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
| Count of unique values | Excel Discussion (Misc queries) | |||
| Unique Count when Values 0.01 | Excel Worksheet Functions | |||
| Count unique values | Excel Worksheet Functions | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
Linear Mode
