Counting text which meets a criteria

[Sai Krishna]

Hi,

I have a column which displays percentages from 10% to 50%. Next column
displays a text which repeats several times in that column against each
percentage.

In the answer column, I am required to first state the number of occurance
between a range of percentages and the number of occurance of the texts the
fall in that range.

Per Text
10% a
11% b
12% c
13% b
14% c

Note: Showing only part of the list

In my answer sheeti have something like this
Range Count
from to
10% 18% 9
19% 22% 4
23% 25% 3
26% 28% 3

Now I also need to show next to the count column how many "a", "b" and "c"
fall against each of the ranges
regards
sai

 

[Mike H]

Hi,

So we have percentage in col A and person in Col B, Try this

=SUMPRODUCT((B1:B20="a")*(A1:A20>=0.1)*(A1:A20<=0.18))

Note I've used the decimal equavalent for the percentage and the formula
does the 10% to 18% range for person A.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

With your data in Sheet1 and the answer sheet as Sheet2..

In the answer sheet(say Sheet2) with start % in cell A1 and end % in cell
B1; try the below formula in cell C1

=SUMPRODUCT((Sheet1!A1:A100>=10%)*(Sheet1!A1:A100<=18%)*
(Sheet1!B1:B100={"a","b","c"}))