Calculating Question

[RJ Swain]

Thank you ahead of time for your help and assistance.

I am working on a worksheet right now where I have a column showing the
percentage of goal vs. target. I would like to make a formula that would add
up all the percentages that are 100% or more over those goals.

Currently, I have the following formula but I am not getting an accurate
count.

=SUMIF(O45:O62,">100%")

One example I had 5 goals over 100% and only 3 showed up. One goal was 157%
and other was 125%

 

[Pete_UK]

Shouldn't you be using COUNTIF rather than SUMIF?

Pete

 

[Fred Smith]

Your formula should work -- it worked properly when I tested it.

Give us a sample of your data, the result you got, and the result you expected.
You confuse us when you say "I had 5 goals over 100% and only 3 showed up". Are
you wanting to count the goals over 100%, or sum them?

 

[RJ Swain]

Sorry about that. Here is some data:

A1 = 101%
B1 = 152%
C1 = 109%
D1 = 100%
E1 = 85%
F1 = 125%

With these percentages, it only shows 3 in the sumif cell when it should
show 5 being over 100% I thought the ">100%" would be the answer but it is
not. If I change 152% to 105% it will then show 4 and not 3. I would like it
to simply look at the percentage column and if it is over 100% add it up and
give me a total of targets over 100%

 

[Fred Smith]

Given your data, if you want to "show 5 being over 100%", then you want Countif,
not Sumif.

However, that still doesn't explain how you got 3 using Sumif. When I type in
your sample data, and use the formula =sumif(A1:F1,">100%"), I get the correct
answer of 4.87.

For us to help you further, you'll need, as requested in my last post, to
provide the formula you are using. Also, tell us how the cell is formatted.

 

[RJ Swain]

Using the =sumif formula I put in the following percentages:
100%, 102%, 120%, 150%, 200%, 100%, 120%, 45%, 100%
the total I got was 7 not 8. So am I missing some sort of wildcard for it to
count percentages over 100%?

Using the countif command it only shows 5 out of the 8 100% figures.

 

[Fred Smith]

Sorry, RJ. I can't help you any further. When I enter the data, I get the
correct results.

You either need to share with us your formula, and your cell format, or you're
on your own.