Counting text which meets a criteria

  • Thread starter Thread starter Sai Krishna
  • Start date Start date
S

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
 
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.
 
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"}))
 
Back
Top