Formulas
 

Hi - I have a work sheet with info as follows:
ColA ColB
A Smith £50.00
A Smith £40.00
D Jones £30.00

I want to count the number of people with packages between certain amounts
ie, £1 - £20, £21 - £50, £51 - £90, but if a person is on twice, i want them
to be counted as one, with the amounts added together. I want to show the
answers on another sheet and the results would be as follows:
ColA ColB
1-20 0
21-50 1
51-90 1

Is this possible?

Thanks in advance!

Becks

Formulas
 

Assume your data looks this:
Cust Amt
Smith 12
Jones 43
Taylor 48
Smith 8
Miller 24
Taylor 3
Miller 12
Create a Pivot Table
Layout: Row field = Cust, Data field = Sum of Amt
Options: Uncheck Grand totals, Autoformat
You should end up with this:

Cust Total
Jones 43
Miller 24
Smith 20
Taylor 51

Create 2 columns like this
Bins Hits
20 1
50 2
90 1

Select the numbers in the Pivot Table and name them ArrayP.
Name Bins.
Select the 3 cells under Hits and enter this array formula with
Ctrl+Shift+Enter:
=FREQUENCY(ArrayP,Bins)

Formulas
 

I think i have a solution.

I made a list myself, with names and amoounts, and repeated some of the names.
I then sorted the list using the autofilter by the name column into
ascending order.
My list is now in columns D & E.

In cell F1 i entered 1, and in cell F2 i entered =IF(D2=D1,F1,F1+1), then i
copied the formula in cell F2 down the length of my list (which is only 12
cells in my example case)

In cell G2 i entered =IF(F1=F2,"",(SUMIF(F$1:F$12,F1,E$1:E$12))), then i
copied the formula in cell G2 down the length of the list.

In column I i put my required ranges, i.e. 0-20, 21-50, 51-90 etc.
In J1 i entered =COUNTIF(G1:G13,"<=20")
In J2 i entered =COUNTIF(G1:G12,"<=50")-COUNTIF(G1:G12,"<=21")
where 50 is the top of the range and 21 is the bottom, in cells J3, J4 etc i
entered the other ranges to count how many enteries.

You could then move these cells to which ever work sheet or location you
want, you may have to play around with the absolute referencing?

"Becks" wrote:

Hi - I have a work sheet with info as follows:
ColA ColB
A Smith £50.00
A Smith £40.00
D Jones £30.00

I want to count the number of people with packages between certain amounts
ie, £1 - £20, £21 - £50, £51 - £90, but if a person is on twice, i want them
to be counted as one, with the amounts added together. I want to show the
answers on another sheet and the results would be as follows:
ColA ColB
1-20 0
21-50 1
51-90 1

Is this possible?

Thanks in advance!

Becks