| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding up with array formula
In A1:A9 a have some values that are either numbers, texts or blanks
In B1 I have =IF(COUNTIF($A$1:A1;A1)=1;1;0) which I copy down This formula shows 1 when a value appears for the first time 0 when the value is blank or appears for second, third... time Finally, I add the range B1:B9 to count the number of different values (not blank) that my range has. My problem is: I want to obtain the same sum with an array formula, without intermediate results. No matter how hard I try, I am unable to get get. If possible, I'd like that the array formula resembles my original formula =IF(COUNTIF($A$1:A1;A1)=1;1;0) . Can anybody help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding up with array formula
So you're counting the number of unique entries in that range (a1:a9)?
=SUMPRODUCT((A1:A9<"")/COUNTIF(A1:A9,A1:A9&"")) vsoler wrote: In A1:A9 a have some values that are either numbers, texts or blanks In B1 I have =IF(COUNTIF($A$1:A1;A1)=1;1;0) which I copy down This formula shows 1 when a value appears for the first time 0 when the value is blank or appears for second, third... time Finally, I add the range B1:B9 to count the number of different values (not blank) that my range has. My problem is: I want to obtain the same sum with an array formula, without intermediate results. No matter how hard I try, I am unable to get get. If possible, I'd like that the array formula resembles my original formula =IF(COUNTIF($A$1:A1;A1)=1;1;0) . Can anybody help? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding up with array formula
Try this:
=SUMPRODUCT((A1:A9<"")/COUNTIF(A1:A9,A1:A9&"")) Counts the uniques in the range. Biff "vsoler" wrote in message oups.com... In A1:A9 a have some values that are either numbers, texts or blanks In B1 I have =IF(COUNTIF($A$1:A1;A1)=1;1;0) which I copy down This formula shows 1 when a value appears for the first time 0 when the value is blank or appears for second, third... time Finally, I add the range B1:B9 to count the number of different values (not blank) that my range has. My problem is: I want to obtain the same sum with an array formula, without intermediate results. No matter how hard I try, I am unable to get get. If possible, I'd like that the array formula resembles my original formula =IF(COUNTIF($A$1:A1;A1)=1;1;0) . Can anybody help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| array formula | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| adding cells within an array | Excel Worksheet Functions | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
| Adding rows to an array | Excel Worksheet Functions | |||
Hybrid Mode
