Excel Function: COUNTIF Problems

  • Thread starter Thread starter jbaden
  • Start date Start date
J

jbaden

71.43% 28.57%
A B
1 50.00 50.00
2 50.00 50.00
3 50.00 50.00
4 50.00 38.01
5 50.00 50.00
6 50.00 46.85
7 50.00 36.41
8 37.65 50.00
9 33.48 39.74
10 30.74 37.51
11 50.00 50.00
12 27.32 34.39
13 50.00 32.13
14 32.46 36.26
Cuttoff 39.00 36.00


I am counting how many times a number comes up under 39 in column A and
how many times a number comes up in column B under 36. Thus the
percentages at the top of the page. I want to combine both column A and
B to create one column and only one positive will be counted for each
row. I want to discrimante if for example, you take row 12. Both
numbers in row 12 would be counted because they are under 39 and 36 for
there respected columns. I need to know how to discriminate against
this so it is only counted once in the percentage at the top when I
combine the positives in the columns????? Only one positive for each
row is counted!!!
 
Jbaden,

You can use your COUNTIF's in combination with a SUMPRODUCT
=COUNTIF(A1:A14,"<" & A15) + COUNTIF(B1:B14,"<" & B15)
- SUMPRODUCT((A1:A14<A15)*(B1:B14<B15))

The COUNTIF's will add 1 each time a number is <39 (A) or <36(B) and
SUMPRODUCT will subtract 1 each time both conditions are met.

Dan E
 
Back
Top