countif on sales banding

Zubair

Active Member
Joined
Jul 4, 2009
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I want number of customers against each band shown positive & negative performance in percentage, please help

Customer No.% Diff
Customer 1
-3.39%​
Customer 2
2.94%​
Customer 3
-4.49%​
Customer 4
42.43%​
Customer 5
5.24%​
Customer 6
5.13%​
Customer 7
10.26%​
Customer 8
-10.40%​
Customer 9
4.25%​
Customer 10
-8.97%​
Customer 11
1.46%​
Customer 12
12.44%​
Customer 13
-33.33%​
Customer 14
-24.86%​
Customer 15
-14.27%​
Customer 16
25.40%​
Customer 17
-20.54%​
Customer 18
8.33%​
Customer 19
13.42%​
Customer 20
25.57%​
Customer 21
-18.47%​
Customer 22
3.53%​
Customer 23
0.42%​
Customer 24
42.75%​
Customer 25
46.02%​
Customer 26
15.81%​
Customer 27
7.00%​
Customer 28
-9.36%​
Customer 29
31.75%​
Customer 30
10.36%​
Customer 31
-15.58%​
Customer 32
-12.64%​
Customer 33
-3.74%​
Customer 34
3.09%​
Customer 35
-13.44%​
Customer 36
25.85%​
Customer 37
-4.02%​
Customer 38
-24.83%​
Customer 39
24.89%​
Customer 40
-10.00%​
Customer 41
-9.09%​
Customer 42
-21.61%​
Customer 43
-22.07%​
Customer 44
2.35%​
Customer 45
-16.00%​
Customer 46
-9.84%​
Customer 47
18.62%​
Customer 48
-17.81%​
Customer 49
-11.34%​
Customer 50
-5.04%​
Banding
BandCount
>-50%
>-40% <-50%
>-10% <-40%
>-0% <-10%
>0% <10%
>10% <40%
>40% <50%
>50%
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
With the main data table in A1:B51
Book1
AB
54Banding
55BandCount
56-50%0
57-40%0
58-10%16
590%9
6010%11
6140%11
6250%3
63100%0
Sheet2
Cell Formulas
RangeFormula
B56:B63B56=COUNTIF($B$2:$B$51,"<="&A56)-SUM(B$55:B55)
 
Upvote 0
Solution

Forum statistics

Threads
1,214,915
Messages
6,122,214
Members
449,074
Latest member
cancansova

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top