Formula Question

[Tony]

Was wondering if there is an IFcount formula for this scenario?


Below on Sheet 1

A B C
1 125 58 183
2 896 876 20
3 500 11 511
4 244 5 249

Then on sheet two

A
1 would like this vale to be the count of b1:b4 if c1:c4 is greater or
equal to 98%. In this case it would be 3 because on sheet 2 c2 is 97.8% of
the difference of b2 and a2. I hope I explained it correctly.

Thank You in advance......

 

[David Biddulph]

Well, I don't understand your example, because C2's value of 20 seems to be
100% of A2-B2, not 97.8%.

But if your C2 value became 19.56 rather than 20, you would get 3 from the
formula =SUMPRODUCT(--(C1:C4/(A1:A4-B1:B4)>=98%))

 

[Tony]

I am using the forumla =SUM(B1/A1) in column "D" and that is how I came up
with 98%. I tried your formula and it returned a number of 4. It should of
returned 2 due to the 1.2% and 5.1% based on below. Let me know if i am way
off track here. I do not need column "D" it is there just as an example.
Thanks for looking in to this....

A B C D E
896 875 21 97.7%
542 500 42 92.3%
896 11 885 1.2%
156 8 148 5.1%

4 (here is where I put in your formula)

 

[David Biddulph]

If you are now interested in B1/A1, when last time round you were interested
in C1/(A1-B1), you'll have to change the formula correspondingly.
When you ask a different question, you'll need a different answer.
Your example this time is of course totally different from the example you
gave last time round.

Note also that you'll need to think about what you want the SUM function to
do for you in SUM(B1/A1). In what way do you expect =SUM(B1/A1) to differ
from =B1/A1? Have you looked at Excel help to see what the SUM function
does?

 

[Tony]

maybe this will help. row "A" is the membership from the previous month.
Row "B" is adds for the current month. Row "C" is the difference between the
two. So I would like to know what groups have 98% or higher of membership
left, so if the group only has 2% or less left that month then I do not want
to count them.