"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!