Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |