summing values according to name in defferent column

  • Thread starter Thread starter Rob Barnard
  • Start date Start date
R

Rob Barnard

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
 
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
 
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, 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
 
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
 
Back
Top