LOOKUP DATA

  • Thread starter Thread starter Raymond Wood
  • Start date Start date
R

Raymond Wood

I have a formula, which will always return a number
between 1 and 20.

If it returns a 1, I want a further formula to look at all
the data in the range $C$5:$C$100, and find the number
of "a"'s that appear.

If it returns a 2, I want a further formula to look at all
the data in the range $D$5:$D$100, and find the number
of "a"'s that appear.

If it returns a 3, I want a further formula to look at all
the data in the range $E$5:$E$100, and find the number
of "a"'s that appear.

and so on.....

up to...

If it returns a 20, I want a further formula to look at
all the data in the range $V$5:$V$100, and find the number
of "a"'s that appear.



The range $C$5:$V$100 has manual input of data, and each
entry can consist
of "a", "b", "ab", "abc", "de", "abcdefghijk", etc, so the
formula has to FIND the number of times that "a" appears
(assume that I am putting this formula in cell B110 and
that A110 contains an "a", and I will copy and paste this
down, so that the formula will claculate for "b", "c",
etc).

Is there a way of doing this?
 
One way

=COUNTIF(OFFSET($C$5,,A2-1,100,),"a")

where A2 holds the formula that returns 1-20, note that it will find
a cell with just a and nothing more, if there can be other characters as
well ala [a123] etc change to

=COUNTIF(OFFSET($C$5,,A2-1,100,),"*a*")

replace A2 with the cell that holds your number formula
 
Back
Top