Sumif based on percentage

[Vcoppens]

Hi, I need to add up one column if the percentage in another is below a certain percentage.

I am able to use the formula =sumif(A1:A10,"<"& A12,B1:b10)*A12

Column B has percentages calculated
cell A12 is the percentage (that changes every month)
Because the percentage is not an even number say 41.67%
It won't do an exact match with the percentages in column B

Any help would be appreciated.

 

[Joe4]

So what exactly is the problem if your formula is working?

It might be helpful if you could post a small data example, along with your expected results (and an explanation of how you get to those results).

 

[Vcoppens]

Is this helpful?:
Current amount (column A) 20
Goal amount (column B) 48
Percentage (column C) =A1/B1 resulting in 41.67%

I need to add all amounts in column A if they are below 41.67%. This percentage is also entered in a cell below the table as it changes every month.
Then I need to add the total of goal amounts that has not reached the percentage and multiply it by the percentage.

Somehow it adds column A (and B) INCLUDING the amounts where the percentage of 41.67% has been met. Although the formula clearly states <

I had a similar issue when conditional format anything above the percentage. I needed to use ROUND (VALUE(ROUND(C2,4))>=$A$5) to work around this.

I think it is a similar issue but cannot figure it out.

Thank you very much

 

[Joe4]

Is this helpful?:

Not quite.

Your original formula seems to be applied to a whole range of cells (rows 1-10). You have seem to have posted a sample for a single row.
And you were comparing to cell A12. And column C was not part of the original equation.
So it is very unclear what you are comparing to what.

Can you please post an ENTIRE example, where we can see values that should be included and values that should not, and what they are being compared to?

 

[Vcoppens]

Sorry I am working thru my cellphone. But hope this helps. I am using 3 columns and in this example 5 rows with data.

The calculation is below the data.
In cell A7 I write down the % that changes every month. I this example I will use 41.67%

In cell B7 I calculate the potential under 41.67% using below formula
=sumif(C1:C5,”<“&A7,B1:B5)*A7
In cell C7 I calculate the actual number under the 41.67% using below formula
=sumif(C1:C5,”<“&A7,A1:A5)

Column A (year to date)
8
29
20
32
26

Column B (target)
19
69
48
77
26

Column C (% to target using =A1/B1)
42.11%
42.03%
41.67%
41,56%
41.27.%

Problem is that it should not count the number in the row with the 41.67% but it includes it.

Hope this made it a little clearer.

 

[Joe4]

Problem is that it should not count the number in the row with the 41.67% but it includes it.

It is because your calculation is really 41.66666666%; you are only electing to show two decimals. Calculations are done against actual values, not displayed values.

Try updating the formulas in column C to round, i.e.

=ROUND(A1/B1,4)

Note that you need to round to 4 decimals, not 2, because 41.67% is really equal to .4167.

 

[Gerald Higgins]

That's your basic maths for you.

The value in row 3 is not really 41.67%, it's really 41.666666.......%, which is indeed less than 41.67%.
It's just (probably!) formated in Excel to appear as 41.67%.

If this is a problem for you, you could consider modifying your formula so that it does some kind of rounding, perhaps
=round(a1/b1,4)

Edit - oops, too slow

 

[Vcoppens]

And that is exactly how it should have been done!
I was trying to tweak my sumif formula.

Thank you soooooooo much!!!

 

[Gerald Higgins]

Hth, tftf

 

[Joe4]

You are welcome.
Glad we were able to help!