| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if unique
Hi all, thank you in advance for your help ,and help in past.
I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | A | B | C | 10 N/A yes 10 N/A yes 11 N/A no 11 N/A no 12 N/A yes 13 N/A no 13 N/A no 13 N/A no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
shouldn't the formula in your example return 1? as there are two 10's
if so try: =SUMPRODUCT((COUNTIF($A$22:$A$29,$A$22:$A$29)=1)*( $B$22:$B$29="yes")) OR =SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$2:$B$ 9="yes"),1,)) (CTRL+SHIFT+ENTER this formula as it is an array-formula (instead of simply entering it). If you insert it correctly curly braces { } will show) pls click YES if this post helped you On 21 Kwi, 14:34, "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: *| * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 12 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
With your posted data in A2:C10
and E2: (the invoice status to find...eg Yes) This regular formula returns the count of unique invoices numbers with a status of E2 (yes) E1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) Using your data, the formula returns: 2 Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Gareth.Evans" wrote in message ... Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | A | B | C | 10 N/A yes 10 N/A yes 11 N/A no 11 N/A no 12 N/A yes 13 N/A no 13 N/A no 13 N/A no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
On Apr 21, 1:47*pm, Jarek Kujawa wrote:
shouldn't the formula in your example return 1? as there are two 10's if so try: =SUMPRODUCT((COUNTIF($A$22:$A$29,$A$22:$A$29)=1)*( $B$22:$B$29="yes")) OR =SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$2:$B$ 9="yes"),1,)) (CTRL+SHIFT+ENTER this formula as it is an array-formula (instead of simply entering it). If you insert it correctly curly braces { } will show) pls click YES if this post helped you On 21 Kwi, 14:34, "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: *| * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 12 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - No, it would return 2 because both invoice 10 and invoice 12 passed. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
On Apr 21, 1:57*pm, "Ron Coderre" wrote:
With your posted data in A2:C10 and E2: (the invoice status to find...eg Yes) This regular formula returns the count of unique invoices numbers with a status of E2 (yes) E1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) Using your data, the formula returns: 2 Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Gareth.Evans" wrote in message ... Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * *10 * * * * * * * * * *N/A * * * * * * * * yes * * * * *10 * * * * * * * * * *N/A * * * * * * * * yes * * * * *11 * * * * * * * * * *N/A * * * * * * * * no * * * * *11 * * * * * * * * * *N/A * * * * * * * * no * * * * *12 * * * * * * * * * *N/A * * * * * * * * yes * * * * *13 * * * * * * * * * *N/A * * * * * * * * no * * * * *13 * * * * * * * * * *N/A * * * * * * * * no * * * * *13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - Sorry Rin i do not understand what you mean for cell E2? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
On Apr 21, 1:57*pm, "Ron Coderre" wrote:
With your posted data in A2:C10 and E2: (the invoice status to find...eg Yes) This regular formula returns the count of unique invoices numbers with a status of E2 (yes) E1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) Using your data, the formula returns: 2 Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Gareth.Evans" wrote in message ... Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * *10 * * * * * * * * * *N/A * * * * * * * * yes * * * * *10 * * * * * * * * * *N/A * * * * * * * * yes * * * * *11 * * * * * * * * * *N/A * * * * * * * * no * * * * *11 * * * * * * * * * *N/A * * * * * * * * no * * * * *12 * * * * * * * * * *N/A * * * * * * * * yes * * * * *13 * * * * * * * * * *N/A * * * * * * * * no * * * * *13 * * * * * * * * * *N/A * * * * * * * * no * * * * *13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - Sorry ignore my last post, i am playing witht he formula now |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
On Apr 21, 1:57*pm, "Ron Coderre" wrote:
With your posted data in A2:C10 and E2: (the invoice status to find...eg Yes) This regular formula returns the count of unique invoices numbers with a status of E2 (yes) E1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) Using your data, the formula returns: 2 Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Gareth.Evans" wrote in message ... Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * *10 * * * * * * * * * *N/A * * * * * * * * yes * * * * *10 * * * * * * * * * *N/A * * * * * * * * yes * * * * *11 * * * * * * * * * *N/A * * * * * * * * no * * * * *11 * * * * * * * * * *N/A * * * * * * * * no * * * * *12 * * * * * * * * * *N/A * * * * * * * * yes * * * * *13 * * * * * * * * * *N/A * * * * * * * * no * * * * *13 * * * * * * * * * *N/A * * * * * * * * no * * * * *13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - Ron: i have a question regarding part of this equation you gave me. =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) What is the 1+ROW(A2) bit about? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
this array-entered formula
=SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$22:$B $29="yes"),1,))+SUM (IF(($B$2:$B$9="yes")*(COUNTIF($A$2:$A$9,$A$23:$A$ 29)1),1,))/SUM(IF (($B$2:$B$9="yes")*(COUNTIF($A$2:$A$9,$A$2:$A$29) 1),1,)) will bring required result unless you have blanks in your range On 21 Kwi, 15:05, "Gareth.Evans" wrote: On Apr 21, 1:47*pm, Jarek Kujawa wrote: shouldn't the formula in your example return 1? as there are two 10's if so try: =SUMPRODUCT((COUNTIF($A$22:$A$29,$A$22:$A$29)=1)*( $B$22:$B$29="yes")) OR =SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$2:$B$ 9="yes"),1,)) (CTRL+SHIFT+ENTER this formula as it is an array-formula (instead of simply entering it). If you insert it correctly curly braces { } will show) pls click YES if this post helped you On 21 Kwi, 14:34, "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: *| * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 12 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - No, it would return 2 because both invoice 10 and invoice 12 passed.- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
Hi, Gareth
Regarding the "-1+ROW(A2)" section of this formula: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0) -1+ROW(A2))=ROW(A2:A10)))) The MATCH function is returning a ordinal position in the list 1st, 2nd, etc. We are comparing that value to the Row Numbers of the range. Consequently, we need to either adjust the ordinal values to align with the Row Numbers...OR...adjust the Row Numbers to align with the ordinal values. For example, the MATCH value of the 1st item is 1...but, the value resides on Row_2. Since I chose to adjust the ordinal values: 1-1+ROW(A2) = 1-1+2 = 2...to match the row number: 2. Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Gareth.Evans" wrote in message ... On Apr 21, 1:57 pm, "Ron Coderre" wrote: With your posted data in A2:C10 and E2: (the invoice status to find...eg Yes) This regular formula returns the count of unique invoices numbers with a status of E2 (yes) E1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) Using your data, the formula returns: 2 Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Gareth.Evans" wrote in message ... Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | A | B | C | 10 N/A yes 10 N/A yes 11 N/A no 11 N/A no 12 N/A yes 13 N/A no 13 N/A no 13 N/A no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - Ron: i have a question regarding part of this equation you gave me. =SUMPRODUCT(--ISNUMBER(1/((MATCH(A2:A10&$E$2,A2:A10&C2:C10,0)- 1+ROW(A2))=ROW(A2:A10)))) What is the 1+ROW(A2) bit about? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
=SUM(N(FREQUENCY(IF(B1:B10="yes",MATCH(A1:A10&"",A 1:A10&"",0)),MATCH(A1:A10&"",A1:A10&"",0))0))
ctrl+shift+enter, not just enter "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | A | B | C | 10 N/A yes 10 N/A yes 11 N/A no 11 N/A no 12 N/A yes 13 N/A no 13 N/A no 13 N/A no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
On Apr 21, 3:24*pm, Teethless mama
wrote: =SUM(N(FREQUENCY(IF(B1:B10="yes",MATCH(A1:A10&"",A 1:A10&"",0)),MATCH(A1:A10*&"",A1:A10&"",0))0)) ctrl+shift+enter, not just enter "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: *| * * * * A * * * * * | * * * * * B * * * * | * * * * C * * * *| * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 10 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 11 * * * * * * * * * *N/A * * * * * * * * no * * * * * 12 * * * * * * * * * *N/A * * * * * * * * yes * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no * * * * * 13 * * * * * * * * * *N/A * * * * * * * * no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - Perfect! Thanks Teethless mama, your answer is simple and elegent, and works like a charm. You made me day. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
Hello,
Another solution which requires VBA, though: =SUMPRODUCT(--(INDEX(Pfreq(A1:A8,C1:C8),,2)="yes")) Pfreq you can get he http://www.sulprobil.com/html/pfreq.html Regards, Bernd |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
You're Welcome!
"Gareth.Evans" wrote: On Apr 21, 3:24 pm, Teethless mama wrote: =SUM(N(FREQUENCY(IF(B1:B10="yes",MATCH(A1:A10&"",A 1:A10&"",0)),MATCH(A1:A10Â*&"",A1:A10&"",0))0)) ctrl+shift+enter, not just enter "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: | A | B | C | 10 N/A yes 10 N/A yes 11 N/A no 11 N/A no 12 N/A yes 13 N/A no 13 N/A no 13 N/A no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - Perfect! Thanks Teethless mama, your answer is simple and elegent, and works like a charm. You made me day. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if unique
forget this one
I might have thought of sth. better than that ;-) On 21 Kwi, 15:40, Jarek Kujawa wrote: this array-entered formula =SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$22:$B $29="yes"),1,))+SUM (IF(($B$2:$B$9="yes")*(COUNTIF($A$2:$A$9,$A$23:$A$ 29)1),1,))/SUM(IF (($B$2:$B$9="yes")*(COUNTIF($A$2:$A$9,$A$2:$A$29) 1),1,)) will bring required result unless you have blanks in your range On 21 Kwi, 15:05, "Gareth.Evans" wrote: On Apr 21, 1:47Â*pm, Jarek Kujawa wrote: shouldn't the formula in your example return 1? as there are two 10's if so try: =SUMPRODUCT((COUNTIF($A$22:$A$29,$A$22:$A$29)=1)*( $B$22:$B$29="yes")) OR =SUM(IF((COUNTIF($A$2:$A$9,$A$2:$A$9)=1)*($B$2:$B$ 9="yes"),1,)) (CTRL+SHIFT+ENTER this formula as it is an array-formula (instead of simply entering it). If you insert it correctly curly braces { } will show) pls click YES if this post helped you On 21 Kwi, 14:34, "Gareth.Evans" wrote: Hi all, thank you in advance for your help ,and help in past. I am trying to count the number of "yes" in column C if Column A has a unique value, see below: Â*| Â* Â* Â* Â* A Â* Â* Â* Â* Â* | Â* Â* Â* Â* Â* B Â* Â* Â* Â* | Â* Â* Â* Â* C Â* Â* Â* Â*| Â* Â* Â* Â* Â* 10 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* yes Â* Â* Â* Â* Â* 10 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* yes Â* Â* Â* Â* Â* 11 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* no Â* Â* Â* Â* Â* 11 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* no Â* Â* Â* Â* Â* 12 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* yes Â* Â* Â* Â* Â* 13 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* no Â* Â* Â* Â* Â* 13 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* no Â* Â* Â* Â* Â* 13 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*N/A Â* Â* Â* Â* Â* Â* Â* Â* no Basically this is part of verifying invoices, A being the invoice number, each invoice can have mnore then 1 entry thus there being two 10's. Column C represtent weather that invoice entry has been a success or not. What i need to know is the number of invoices that were a success (Column c) So.... in the example above the formula would return 2, as invoice 10 and 12 were a success but not he others. I hope i have made sense, i have been trying to figure this out for a few days noe, i have got as far as working out the number of unique entriers in column a by useing array formulas, but get stuck. Hope someone can help Many Thanks- Hide quoted text - - Show quoted text - No, it would return 2 because both invoice 10 and invoice 12 passed.- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count unique | Excel Worksheet Functions | |||
| count unique | Excel Worksheet Functions | |||
| count unique | Excel Worksheet Functions | |||
| Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
| Count Unique Values | Excel Worksheet Functions | |||
Linear Mode
