summing values according to name in defferent column
 

Hi,

You don't need a macro to do this; you can make use of
formula arrays.

Set up a fifth column which is just quantity * price

On your summary page type in formulaa similar to (not
forgetting to add worksheet references):

SUM((A2:A6="name1")*(B2:B6="A")*(E2:E6))
SUM((A2:A6="name1")*(B2:B6="D")*(E2:E6))
SUM((A2:A6="name2")*(B2:B6="A")*(E2:E6))
SUM((A2:A6="name2")*(B2:B6="D")*(E2:E6))

You'll need to press Shift+Ctrl+Enter to enter the formula
otherwise you may get incorrect results.

To solve your problem with name changes you should
reference the names from the summary sheet rather than
hard coding them into the formulae.

You can count the number of operations using:

CountIf(A2:A6,"name1")

Hope this is useful

:-)

Rob



-----Original Message-----
Hello, I hope somebody can help.
I have a sheet ordered in the following way

client sign quantity price
name1 A 3 12
name1 D 4 57
name2 A 2 12
name2 A 4 100
name2 D 3 10

where A means the client has bought something and D means
he is
selling.
I would need a macro that will give on a new sheet the
output of the
total operations of the client and how many operations he
has done.
Example

cilent total bought total sold n. of operations
name1 36 (i.e. 3x12) 228 2
name2 424 30 3


Unfortunately, the names change every now and then, so
inserting the
name in the macro would eventually prove pointless.
I have some experience with VB, but am totally at loss
with this
problem (obviously I'm not as good as I wish I were).
Thank you very
much for any help you might be able to give me.
Lorenzo
.

summing values according to name in defferent column
 

Don, Rob, thank you both for your help and your time, I really
appreciate it. Still, I'm not to sure what to do with the formulae (I
know this sounds weird, but I feel a lot more at ease with macros).
Rob, thanks for the suggestion on the new column with the total value
Referencing the names would take way too long (there are some 1300
names). Besides, new names are added every now and then, meaning I
would have to explain the process to every other person using the file
(and the names in the original DB are retreived from an AS400). What I
had in mind was a macro that worked sort of like this:

If name in next cell is the same and value in column B is A, then
sum value in col. F
until next cell = blank

And make a second similare macro for the D operations, adding for both
something that will count how many cells have been summed.
I hope this makes sense and that you may be able to help me - VB
knowledge just seems to be something I will never fully acheive.
Again, thank so much for all your help.
Lorenzo

summing values according to name in defferent column
 

I'm a little bit confused about what you are trying but this will solve the
2 criteria problem.
THIS IS WHAT I RECOMMENDED IN MY FIRST REPLY. LOOK BACK.
Instead of sumif use sumproduct. Cannot and shouldn't anyway use the B:B
=sumproduct(($b$2:$b$2000="name1")*($d$2:$d$2000=" A")*$m$2:$m$2000)

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Lorenzo" wrote in message
om...
Don, again thank you very much for your help. As you said, formulas
are definetly the way to go, and I was thinking of keeping the thing
simple by having the sums made by formulas on the same sheet, hide the
column with the formulas and have a simply copy and paste function
between the two sheets (which, I forgot to mention, are on two
different workbooks). Not understanding too much what your formula did
(I'm a bit thick) I started fooling around with the insert function
comand menu to see what I would come up with, and I found that the
SUMIF formula is pretty close to what I need. As a matter of fact,
it's exactly what I need, except for two slight glitches. As it is, my
formula is

=SUMIF(B:B,"=name1,M:M)

where B is the column the names of the clients is, and M is the column
where the transaction value is.
Again, I'm stuck on the same two problems, meaning:
1. I have to manually add every single client's name and have a
separate row for each formula, but if this is the only way, so be it.
2. Most importantly, I don't seem to be able to have the formula work
on two criterias, meaning that as it is, it sums both sales and
purchases (not excatly what I had in mind). Therefor, I would need the
formula to check if the value in column D is either A or D.

Again, any suggestions?
Thanks a lot for all your help - I really hope your not losing too
much time on this imbecille that can't even figure out a double
criteria problem on his own...
Lorenzo

summing values according to name in defferent column
 

Don, thank you for your help, but I found another way around my
problem. Lets all praise the magic pivot table. I have made a new
workbook with a pivot table that does just what I need, except being
accessible. I have therefore automated a copy and paste to a new sheet
in the same workbook, hiding the pivot table sheet because there's no
point in seeing the ugly side of the data.
This way I can format my colums just the way I like it, make it look
high tech, trendy, professional etc etc...
Still (sigh), I have a ever so slight problem. The pivot table comes
out as follows

client code client name bought sold total
1234 name1 32 12 44
1234count 3 4 7
4321 name2 43 2 45
4321count 5 1 6


So I have my client's operations grouped by name, summed, and counted.
Once imported to text, which makes altering the text a lot more
simple, all i wanted to do was to cut the count values and put them to
the right of the other data, and obtain something like

client code client name bought sold total n
operations
1234 name1 32 12 44 7

4321 name2 43 2 45 6


I'm ok with everything except this - do you know of a VB code that wil
select every second cell (i.e. in a range "C4:E600" select only cells
on even rows)?

Thank you so much again - you have no idea how much this is helping
me.
Lorenzo

summing values according to name in defferent column
 

Lorenzo,

Don's formula is the way to go
=sumproduct(($b$2:$b$2000="name1")*($d$2:$d$2000=" A")*$m$2:$m$2000)

but instead of using "name1", have the names listed in column A (same line
as the formula and replace "name1" with A2001 (assuming the formula is on
line 2001. Than fill the formula down to match the list of names.

This makes it easier to set up the formulas without a lot of typing and also
allows quick changes to the list. Also prevents typos, especially if you
paste the name list from an advanced filter of the data. (I use this all
the time and it works a treat).

steve

"Lorenzo" wrote in message
om...
Don, again thank you very much for your help. As you said, formulas
are definetly the way to go, and I was thinking of keeping the thing
simple by having the sums made by formulas on the same sheet, hide the
column with the formulas and have a simply copy and paste function
between the two sheets (which, I forgot to mention, are on two
different workbooks). Not understanding too much what your formula did
(I'm a bit thick) I started fooling around with the insert function
comand menu to see what I would come up with, and I found that the
SUMIF formula is pretty close to what I need. As a matter of fact,
it's exactly what I need, except for two slight glitches. As it is, my
formula is

=SUMIF(B:B,"=name1,M:M)

where B is the column the names of the clients is, and M is the column
where the transaction value is.
Again, I'm stuck on the same two problems, meaning:
1. I have to manually add every single client's name and have a
separate row for each formula, but if this is the only way, so be it.
2. Most importantly, I don't seem to be able to have the formula work
on two criterias, meaning that as it is, it sums both sales and
purchases (not excatly what I had in mind). Therefor, I would need the
formula to check if the value in column D is either A or D.

Again, any suggestions?
Thanks a lot for all your help - I really hope your not losing too
much time on this imbecille that can't even figure out a double
criteria problem on his own...
Lorenzo