sum values from range of cells if cells correcponding have the sam

[Eisaz]

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,

 

[Max]

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

 

[John C]

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]

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.

 

[T. Valko]

see your other post

 

[Per Jessen]

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

 

[Max]

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
---

 

[Max]

=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
---

 

[John C]

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]

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'.

 

[Max]

.. 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
---

 

[John C]

As stated, volatile functions, such as OFFSET, definitely have their uses,
and in fact, there are many times where one would want/prefer a volatile
function. I have a workbook that I use very often every day that does
want/need the OFFSET function, and I use it gladly. But why would one want a
volatile function when one is not called for? Why would the OP double post
and never respond back that 1/all of the proposed solutions actually worked
for them?

 

[Max]

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

 

[Max]

.. But why would one want a volatile function when one is not called for?

You're taking it further than what was asked by OP as the crux issue.
This wasn't mentioned by OP as an exclusion.

OP said:

I would appreciate any ideas how to do this.

And I merely ventured an option for OP to address his/her crux. That's it.
Ok, so yours is better. But man, did I ever contest that? Look again.

Why would the OP double post and never respond back
that 1/all of the proposed solutions actually worked for them?

OPs are not always as gracious as we would like them to be, I'd guess, John.
As an OP I'd always be gracious to all responders to my query, and I'd thank
& reply to each and every response received in my thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

 

[Louise]

I am trying to post a question and I can't get it to open so I hope you don't
mind me asking you a question.
I have an if formula =if($Z$58="",ag46+ag48+ag50)-if($Z$58="X",0) If cell
z58 is empty I want it to add the ag cells and if there is an x in Z58 I want
it to put in 0. But it is not adding it is only putting in 0 for both
functions. Can you help me?

 

[T. Valko]

Try one of these:

=IF($Z$58="X",0,IF($Z$58="",AG46+AG48+AG50,0))

=($Z$58="")*SUM(AG46,AG48,AG50)