I have following

A B C D and so on
1a 20 30 1000 20 = 100% - no of times there were
values in A,B,C,D
1b 0 500 6000 = 75% - no of times there were
values in A,B,C,D
1c 70 = 25%
2a 900 870 700 650 = 100%
2b 80 438 80 =75%
3 129 203 450 = 75%

How do I get the % of 1a 1b and 1c altogether. Here in this situation above
it is 100% since just looking at 1a I can tell. Is there a formula I can use
that can help me count 1a, 1b and 1c together without double counting, same
for 2a,2b e.t.c. I have multiple data ranging from Cell A to Cell .........
that I need to count the times of number occurence in them without double
counting using 1a, 1b, 1c as 1; 2a,2b as 2 and so on...

All help totally appreciated

It looks messy, but this worked for me:

=COUNT(OFFSET(INDIRECT("B"&MATCH(IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)&"?"),$A$1:$A$6,0)),0,0,COUNTIF($A$1:$A$6,IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)&"?")),4))/(COUNTIF($A$1:$A$6,IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)&"?"))*4)

If the letters after the number ever go to two-letter sequences, or if there
are spaces, this will fail.


"Yossy" wrote:

> I have following
>
> A B C D and so on
> 1a 20 30 1000 20 = 100% - no of times there were
> values in A,B,C,D
> 1b 0 500 6000 = 75% - no of times there were
> values in A,B,C,D
> 1c 70 = 25%
> 2a 900 870 700 650 = 100%
> 2b 80 438 80 =75%
> 3 129 203 450 = 75%
>
> How do I get the % of 1a 1b and 1c altogether. Here in this situation above
> it is 100% since just looking at 1a I can tell. Is there a formula I can use
> that can help me count 1a, 1b and 1c together without double counting, same
> for 2a,2b e.t.c. I have multiple data ranging from Cell A to Cell .........
> that I need to count the times of number occurence in them without double
> counting using 1a, 1b, 1c as 1; 2a,2b as 2 and so on...
>
> All help totally appreciated