Countifs or Sumifs?

[canthony24]

I have a subset of some data below. I am trying to get a count of the items in Column A that total up to 95% and 80% of sales in Column C. I can easily manually drag my cursor down to count the items that make up 95%. But I am trying to automate some reports.

So, what is the count of items that equal 95% of sales and what is count that make up 80%.

I think it's a countifs, but maybe a sumifs? I can't determine which one.



A B C [TABLE="width: 536"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]1 6967590[/TD]
[TD="align: right"]$5,184,968[/TD]
[TD="align: right"]15.25%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]2 0610337[/TD]
[TD="align: right"]$4,430,940[/TD]
[TD="align: right"]13.03%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]3 2786127[/TD]
[TD="align: right"]$4,141,915[/TD]
[TD="align: right"]12.18%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]4 4187357[/TD]
[TD="align: right"]$3,318,491[/TD]
[TD="align: right"]9.76%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]5 7468507[/TD]
[TD="align: right"]$3,107,115[/TD]
[TD="align: right"]9.14%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]6 7468515[/TD]
[TD="align: right"]$3,069,548[/TD]
[TD="align: right"]9.03%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]7 1102724[/TD]
[TD="align: right"]$2,925,702[/TD]
[TD="align: right"]8.61%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]8 6929863[/TD]
[TD="align: right"]$2,741,690[/TD]
[TD="align: right"]8.06%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]9 3598687[/TD]
[TD="align: right"]$2,608,364[/TD]
[TD="align: right"]7.67%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]10 3598661[/TD]
[TD="align: right"]$2,468,473[/TD]
[TD="align: right"]7.26%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[igold]

What you want to do is called a Pareto Analysis and is usually depicted with a chart or a pivot table. You could do it with a helper column that keeps a running total or cumulative total of the percentages.

You might want to Google "Pareto Analysis in Excel" and see which results best suit you.