SPECIFIC WORD COUNT FROM A RANGE

[Guest]

Hi,
I need to count and separate the number of specific clients into lists with
their relative costs only. how can i acheive this? Example:
( say client list is B4:B1000. Project details C4:C1000 & Cost is D41000)
Client1 project details £1000
client2 project details £3500
client1 project details £ 500
client1 project details £2000
client2 project details £1500
client3 project details £4000

my list would be:
3 Client1 £3500
2 Client2 £5000
1 Client3 £4000

any help greatly appreciated.
thanks, Nigel

 

[JulieD]

Hi Nigel

i would recommend using a pivot table ... click on a cell somewhere in your
data and choose data / pivot table and pivot chart report ... choose NEXT,
excel should select your data area, choose NEXT, choose new worksheet and
finish.

Drag "Clients" to where it says "ROW", drag COST to where it says "DATA" and
you'll have your list.

To get an individual listing of each .. double click on the 3500 of Client
1 - a new sheet will be created showing the records that make up that value,
switch back to the pivot table, and double click on the next value & the
third .. you can then print the lists out or whatever.

Check out www.contextures.com/tiptech.html for more details on using pivot
tables.

 

[Guest]

I would probably start off by using an Advanced Filter and copying to another
location to produce a list of unique clients (see
http://www.allaboutoffice.co.uk/exfilter.htm - bottom of the page about
unique records) I would then use the COUNTIF function (
http://www.allaboutoffice.co.uk/excountif.htm ) to count the instances of
each client and then the SUMIF function (
http://www.allaboutoffice.co.uk/exsumif.htm ) to sum each client's project
values.

Andrea Jones

 

[Guest]

Hi,

I cannot use advanced fileter as the sheet where the information is kept
needs to be kept in the same view and cannot be changed. i need to create my
list on a different sheet with the total amount of costs per client. pivot
table is not what i am lookinf for as this needs to adapted to run with some
programming.
summary,
get information from client list on sheet A,
create client list ( with no doubled up client names )
create amount of enquiries per clients in list
create cost per client in list


Regards,

Nigel

 

[JulieD]

Hi Nigel

still can't see why a pivot table won't work ... you can refresh the pivot
table via code to show the lastest information .... and depending on how
much coding you want to do, i think you could automate the generation of the
individual lists (not that i've tried but can't see why not).

if this isn't the type of solution you're looking for, maybe you could
elaborate a bit more on what it is that you want.

 

[Guest]

Hi JulieD,
I have a layout on a sheet which has to stay exactly as it is. I have
created a code to look at the enquiry list from all of our clients together (
one very long list ), This creates me a new list on another sheet. With this
list is the amount of costs in production to date, lost to date and % all
relalive to each individual client. What i need is an amount of enquiries per
client. The pivot tables are not what i am looking for. I just need the
information in an individual cell next to the client summary list.
For example:
Client Amount in Prod Amount Lost % per quote Amount of
Quote
myclient £100,000 £20,000 25%
13
myother £15,000 £ 2,000 7%
4

example shows client spend of £100,000. Loss of £20,000 (quoted) which is
25% of our total yearly quote to date, and 13 enquiries.


Regards,

Nigel

 

[JulieD]

Hi Nigel

from what you're saying then the only thing you are missing is the amount of
enquiries per client .. for this you can use the COUNTIF function.
=COUNTIF(Original_Sheet!$A$1:$A$100,A1)
where the name of the client is in A1 and you want to count how many times
that client's name appears in the client name list on the original sheet.