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!

B 300 19.2%
C 20 1.3%
D 1 0.1%
E 30 1.9%
F 4 0.3%
G 5 0.3%
H 600 38.5%
I 500 32.1%


Ok let put it this way. The above items that drive 80% sell thru in total is item b, H & I. So i need to find out a formula to find which item are driving 80% or more in the above table. So I ended up using =COUNTIF(C2:C10,"10.5%") and the answer is 3 items and the total % from this 3 items is 80% ++. From this total % I'm using =SUMIF(C2:C10,"10.5%"). My question is is that any other way for me to find which items are driving 80% sales?

Here's the workbook.

No, that's a picture of the workbook. It helps nobody!

Until you can provide a workbook with your current formulas and an explanation of WHAT you're trying to achieve and WHY what you currently have isn't good enough, nobody will be able to help you!!!!!!!

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

Hi, my apologize to confused you.

I need to find the fewest items that sum to about 80% or closest to 80% in column C.

"LF.TEN" wrote:
'joeu2004[_2_ Wrote:
And do you want "about" 80% (which might be less); or
do you want "no less than" 80%?
[....]
You might start with the VBA code mentioned at
http://www.sulprobil.com/html/accoun...e_problem.html.
[....]
Actually I want to know the fewest items that sum to about 80%.

The take-away from my previous posting is: this is a very difficult nut to
crack in general, and no simple Excel formula or single function is likely
to produce the desired result except by accident or by coincidence.

It requires an iterative algorithm, best implemented in VBA, IMHO. Even so,
I believe it is a very complex algorithm when designed correctly.

Think about it! Imagine that you have an empty box and a set of
oddly-shaped malleable shapes of varying size. You might start by picking
the largest ones (assuming they fit at all); but eventually you might need
to pick some of the smallest ones to fill the remaining space as best as
possible. Alternatively, you might find a set of shapes that overflows the
box, but by less than the unfilled space with the first set.

If you did this manually, there would be a lot of trial-and-error with a lot
of intuitive thinking going into the selection criteria. No different for a
computer algorithm.

Again, start with the aforementioned VBA code. It might do what you want as
is; if not, it might provide a good starting point for the desired
algorithm.

Good luck!