Sumif & Countif

Originally Posted by LF.TEN

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!

Originally Posted by joeu2004[_2_]

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

Originally Posted by Spencer101

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

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!