If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Sumif & Countif
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Sumif & Countif

#11
July 7th 12, 03:53 PM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 655

Quote:
 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!!!!!!!
#12
July 7th 12, 10:52 PM posted to microsoft.public.excel.misc
 joeu2004[_2_] external usenet poster Posts: 639
Sumif & Countif

"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%")

[....]

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

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

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!

#13
July 8th 12, 01:53 PM
 LF.TEN Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 8

Quote:
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.
Attached Files
 sumif & countif.zip (8.3 KB, 24 views)
#14
July 8th 12, 02:07 PM
 LF.TEN Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 8

Quote:
 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.
Attached Files
 sumif & countif.zip (8.3 KB, 9 views)
#15
July 8th 12, 05:40 PM posted to microsoft.public.excel.misc
 joeu2004[_2_] external usenet poster Posts: 639
Sumif & Countif

"LF.TEN" > wrote:
> 'joeu2004[_2_ Wrote:
>> And do you want "about" 80% (which might be less); or
>> do you want "no less than" 80%?

[....]
>> 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!

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Sumif And Countif roter789 Excel Worksheet Functions 7 August 21st 09 12:00 AM Countif & Sumif JulesHR Excel Worksheet Functions 7 May 29th 08 04:20 PM Countif/Sumif Cain Excel Worksheet Functions 2 February 12th 06 07:59 PM Countif and sumif Visual Excel Discussion (Misc queries) 19 August 10th 05 05:59 PM Sumif for countif? pantelis Excel Programming 5 October 17th 03 11:28 PM

All times are GMT +1. The time now is 05:52 AM.