Count Cells Mutiple Criteria

[kjguillermo]

I need to know how many cells in Column B meet 3 different results using
multiple criteria using the following as an example:


B D G
7 1.5 1000 1000
8 0.89 0 4583
9 0.31 703 5700
10 0.47 4782 3658
11 2.11 235 6258
12 0.77 10421 1266
13 1.00 9688 2293
14 1.05 5691 6632
15 0.00 0 0
16 0.75 0 1197
17 1.99 172 3848
18 2.52 3984 1930
19 1.25 931 3784
20 0.00 0 5230

For the first result, I need to know how many cells are = or<B7 AND >D7 in
cells D820 OR >G7 in cells G8:G20

For the second result, I need to know how many cells are between >B7 and
<B7*1.1 AND >D7 in cells D820 OR >G7 in cells G8:G20

For the third result, I need to know how many cells are >B7*1.1 AND >D7 in
cells D820 OR >G7 in cells G8:G20

Thanks Again For Everyone's Help!!!

 

[Bernie Deitrick]

For your first question, array-enter (enter using Ctrl-Shift-Enter)

=SUM(IF(((D820<=B7)*(D820>D7)+(G8:G20>G7)*1)>0,1,0))

For the second, array enter

=SUM(IF(((D820>B7)*(D820<(B7*1.1))*(D820>D7)+(G8:G20>G7)*1)>0,1,0))

For the third:

=SUM(IF(((D820>(B7*1.1))*(D820>D7)+(G8:G20>G7)*1)>0,1,0))

Note that your language wasn't very specific, so some of the > may need to be >= and some < should
be <=


HTH,
Bernie
MS Excel MVP