hiya, i have a small project and i've spent too much time on this already -
can't figure it out myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to them
values from columns B:E.
this is a sort of result i want to get:
A B C D E F RESULT:
1 A1 1 5 2 3 A1 31
2 B5 2 6 3 3 A2 19
3 C1 3 7 4 3 B1 22
4 A1 4 8 5 3 B5 37
5 B5 5 9 6 3 C1 33
6 C2 6 1 6 3 C2 16
7 A2 7 2 7 3
8 B1 8 3 8 3
9 C1 9 4 0 3

I would appreciate any ideas how to do this. Thanks and regards,

Assume in G1 down are the unique items: A1, A2, etc
Then in H1: =SUMPRODUCT(($A$1:$A$10=G1)*OFFSET($B$1:$B$10,,,,4 ))
Copy H1 down to return required results. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Eisaz" wrote:
hiya, i have a small project and i've spent too much time on this already -
can't figure it out myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to them
values from columns B:E.
this is a sort of result i want to get:
A B C D E F RESULT:
1 A1 1 5 2 3 A1 31
2 B5 2 6 3 3 A2 19
3 C1 3 7 4 3 B1 22
4 A1 4 8 5 3 B5 37
5 B5 5 9 6 3 C1 33
6 C2 6 1 6 3 C2 16
7 A2 7 2 7 3
8 B1 8 3 8 3
9 C1 9 4 0 3

I would appreciate any ideas how to do this. Thanks and regards,

To the OP, this response, while it does work, uses a volatile function. See
my formula for a non-volatile solution. The real headache from volatile
functions occur when you open a file, then do nothing before trying to close
the file, it will ask you if you wish to save changes, since volatile
functions are always calculating.
--
** John C **

"Max" wrote:

Assume in G1 down are the unique items: A1, A2, etc
Then in H1: =SUMPRODUCT(($A$1:$A$10=G1)*OFFSET($B$1:$B$10,,,,4 ))
Copy H1 down to return required results. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Eisaz" wrote:
hiya, i have a small project and i've spent too much time on this already -
can't figure it out myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to them
values from columns B:E.
this is a sort of result i want to get:
A B C D E F RESULT:
1 A1 1 5 2 3 A1 31
2 B5 2 6 3 3 A2 19
3 C1 3 7 4 3 B1 22
4 A1 4 8 5 3 B5 37
5 B5 5 9 6 3 C1 33
6 C2 6 1 6 3 C2 16
7 A2 7 2 7 3
8 B1 8 3 8 3
9 C1 9 4 0 3

I would appreciate any ideas how to do this. Thanks and regards,

To the OP, this response, while it does work, uses a volatile function.

To the OP: Of course it works. I would have tested it carefully before
posting it. And the volatile function used earlier is OFFSET - Thought I'd
just state it here for thread completeness since John omitted mention. It's
a versatile function to me (volatility notwithstanding), and it deserves its
place in the sun. By all means, do go with what you prefer, for example the
shortest, non-volatile amongst the many suggestions which your posting is
fortunate to receive.

To John: Should I thank you for reducing the worth of my response to zero?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

I know from many of your posts that you love the volatile function. And I
also know that almost always after you post a volatile function, someone else
comes along and posts a non-volatile function. Your formula does work, but
95% of the time, the user does not want a volatile function, and in fact,
many times it generates a question "Why does excel ask me if I want to make
changes when I haven't made any changes?" Again, your formula works, and
trust me, I use OFFSET in many of my spreadsheets, but only when I really
don't have a way around it.


--
** John C **

"Max" wrote:

To the OP, this response, while it does work, uses a volatile function.

To the OP: Of course it works. I would have tested it carefully before
posting it. And the volatile function used earlier is OFFSET - Thought I'd
just state it here for thread completeness since John omitted mention. It's
a versatile function to me (volatility notwithstanding), and it deserves its
place in the sun. By all means, do go with what you prefer, for example the
shortest, non-volatile amongst the many suggestions which your posting is
fortunate to receive.

To John: Should I thank you for reducing the worth of my response to zero?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

.. And I also know that almost always after you post a volatile function,
someone else comes along and posts a non-volatile function...

But of course. The inherent graciousness in me is to always allow space for
complementary suggestions by others. This can only add further value and
richness with alternatives.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

Couldn't quite follow where some of your data was, so, assuming your labels
are column A, rows 1-9, and any data relative to those labels are in B1:F9 (I
have all of column F empty).
In cells H2:H7, I typed each individual label: A1 | A2 | B1 | B5 | C1 | C2
In cell I2, I typed the following formula:
=SUMPRODUCT(($A$1:$A$9=H2)*($B$1:$F$9))
and then copied this formula down to I7.
Obviously, lengthen the range beyond 9 as needed :)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Eisaz" wrote:

hiya, i have a small project and i've spent too much time on this already -
can't figure it out myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to them
values from columns B:E.
this is a sort of result i want to get:
A B C D E F RESULT:
1 A1 1 5 2 3 A1 31
2 B5 2 6 3 3 A2 19
3 C1 3 7 4 3 B1 22
4 A1 4 8 5 3 B5 37
5 B5 5 9 6 3 C1 33
6 C2 6 1 6 3 C2 16
7 A2 7 2 7 3
8 B1 8 3 8 3
9 C1 9 4 0 3

I would appreciate any ideas how to do this. Thanks and regards,

=SUMPRODUCT(($A$1:$A$9=H2)*($B$1:$F$9))

Think the parens for the 2nd term can be removed
This shorter one would work equally well:
=SUMPRODUCT(($A$1:$A$9=H2)*$B$1:$F$9)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

I always include the second set of parenthesis. The microscopic amount of
memory you could save by excluding them isn't worth it to make the formula
'cleaner looking'.
--
** John C **

"Max" wrote:

=SUMPRODUCT(($A$1:$A$9=H2)*($B$1:$F$9))

Think the parens for the 2nd term can be removed
This shorter one would work equally well:
=SUMPRODUCT(($A$1:$A$9=H2)*$B$1:$F$9)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

The comments were meant more for the OP, not for you, heavens forbid.
Please continue to do whatever you want. Its a free world.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"John C" <johnc@stateofdenial wrote in message
...
I always include the second set of parenthesis. The microscopic amount of
memory you could save by excluding them isn't worth it to make the formula
'cleaner looking'.
--
** John C **

see your other post

--
Biff
Microsoft Excel MVP


"Eisaz" wrote in message
...
hiya, i have a small project and i've spent too much time on this
already -
can't figure it out myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to
them
values from columns B:E.
this is a sort of result i want to get:
A B C D E F RESULT:
1 A1 1 5 2 3 A1 31
2 B5 2 6 3 3 A2 19
3 C1 3 7 4 3 B1 22
4 A1 4 8 5 3 B5 37
5 B5 5 9 6 3 C1 33
6 C2 6 1 6 3 C2 16
7 A2 7 2 7 3
8 B1 8 3 8 3
9 C1 9 4 0 3

I would appreciate any ideas how to do this. Thanks and regards,

On 17 Nov., 22:41, Eisaz wrote:
hiya, i have a small project and i've spent too much time on this already -
can't figure it out myself. What i need to do is to create a formula which
will find the same values in column A and will sum the corresponding to them
values from columns B:E.
this is a sort of result i want to get:
* * * * A * * * B * * * C * * * D * * * E * * * F * * * RESULT:
1 * * * A1 * * *1 * * * 5 * * * 2 * * * 3 * * * * * * * A1 * * *31
2 * * * B5 * * *2 * * * 6 * * * 3 * * * 3 * * * * * * * A2 * * *19
3 * * * C1 * * *3 * * * 7 * * * 4 * * * 3 * * * * * * * B1 * * *22
4 * * * A1 * * *4 * * * 8 * * * 5 * * * 3 * * * * * * * B5 * * *37
5 * * * B5 * * *5 * * * 9 * * * 6 * * * 3 * * * * * * * C1 * * *33
6 * * * C2 * * *6 * * * 1 * * * 6 * * * 3 * * * * * * * C2 * * *16
7 * * * A2 * * *7 * * * 2 * * * 7 * * * 3 * * * * * * * * * * *
8 * * * B1 * * *8 * * * 3 * * * 8 * * * 3 * * * * * * * * * * *
9 * * * C1 * * *9 * * * 4 * * * 0 * * * 3 * * * * * * * * * * *

I would appreciate any ideas how to do this. Thanks and regards,

Hi

With the list of unique values from colunmn A listed in column F, copy
this formula til G1 and copy down.

=SUMIF($A$1:$A$9,F1,$B$1:$B$9)+SUMIF($A$1:$A$9,F1, $C$1:$C$9)+SUMIF($A
$1:$A$9,F1,$D$1:$D$9)+SUMIF($A$1:$A$9,F1,$E$1:$E$9 )

Hopes it helps

Regards,
Per