Hi, I'm trying to use the countif & sumif to find which item driving the 80% sales.
Do you have any better formula to calculate the 80% accurately. I've attached my working here.

column D
=COUNTIF(C2:C10,"10.5%")

column E
=SUMIF(C2:C10,"10.5%")


Thanks

What do you mean by "driving the 80% sales"?

I'm trying set a formula

I know that!
My question was "what do you mean by 'driving the 80% sales'"?

I don't understand what you're trying to achieve with a formula, and until you can explain the intended results there's not a person out here that is able to help you.

I saw you posted the same question on another Excel forum with far more users than this one and if they couldn't help you without an explanation then nobody can.

Hi, I'm trying to set a formula to find which item are driven 80% sell thru. What I did now is i need to change the 10.5% everything time i added a new data.


column D
=COUNTIF(C2:C10,"10.5%")


column E
=SUMIF(C2:C10,"10.5%")

Post an example workbook showing before and after.

And you need to explain what you mean by "Driven". It makes no sense.

sorry it should be an item driven 80% in total.

That doesn't help anyone understand what you mean by driven!

"LF.TEN" wrote"
Hi, I'm trying to use the countif & sumif to find which
item driving the 80% sales.
Do you have any better formula to calculate the 80%
accurately.
[....]
column D
=COUNTIF(C2:C10,"10.5%")
column E
=SUMIF(C2:C10,"10.5%")
[....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=457|

And it appears that you like to find the number of items whose percentage of
items sold sum to about 80%.

(Note that column C is the percentage per item of total items sold.)

I believe your formulas work only by coincidence. Imagine a situation with
many more items to sell, and none represents more then 10.5% of the total
sold.

First, you need to specify some criteria for the solution. For example, do
you want to know the fewest items that sum to about 80%; or do you want to
know the most items; or do you want the number of items whose sum comes
closest to 80%? And do you want "about" 80% (which might be less); or do
you want "no less than" 80%?

In any case, this is a difficult problem to solve. With very few items,
there is a way to set up Solver to provide __an__ answer, not necessarily
the "best" answer.

But generally, it is requires an algorithm implemented using VBA (i.e. a
macro).

A couple have been mentioned in past discussions. I don't know if any of
them find the "best"; and I don't know if any of them find "no less than" or
if they find "about" (which might be less).

You might start with the VBA code mentioned at
http://www.sulprobil.com/html/accoun...e_problem.html.
Unfortunately, his webpage is difficult to understand, IMHO. But the code
is usable.

Nevertheless, I have not really vetted the algorithm other than to try one
or two simple examples.

Good luck!

Hi, Sorry to make everyone confusing here.
Actually I want to know the fewest items that sum to about 80%. Attached please find my current working workbook with formula.