Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3
Default sum values from range of cells if cells correcponding have the sam

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,
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default sum values from range of cells if cells correcponding have the sam

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,

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,358
Default sum values from range of cells if cells correcponding have the sam

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,

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,358
Default sum values from range of cells if cells correcponding have the

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,

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default sum values from range of cells if cells correcponding have the sam

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,





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 703
Default sum values from range of cells if cells correcponding have thesam

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
  #7   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default sum values from range of cells if cells correcponding have the

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


  #8   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default sum values from range of cells if cells correcponding have the

=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
---
  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,358
Default sum values from range of cells if cells correcponding have the

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



  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,358
Default sum values from range of cells if cells correcponding have the

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



  #11   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default sum values from range of cells if cells correcponding have the

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


  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,358
Default sum values from range of cells if cells correcponding have the

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?
--
** John C **

"Max" wrote:

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



  #13   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default sum values from range of cells if cells correcponding have the

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



  #14   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default sum values from range of cells if cells correcponding have the

.. 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
---
"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **



  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 6
Default sum values from range of cells if cells correcponding have the

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


"Max" wrote:

.. 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
---
"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **






  #16   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default sum values from range of cells if cells correcponding have the

Try one of these:

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

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

--
Biff
Microsoft Excel MVP


"Louise" wrote in message
...
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?
--
Louise


"Max" wrote:

.. 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
---
"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If certain cells not blank, and cells in range are, set values to ktoth04 Excel Discussion (Misc queries) 0 February 21st 08 10:01 PM
Count values in a range of cells Loralei Excel Worksheet Functions 3 November 14th 07 12:13 AM
Find a range of values in a range of cells Jack Taylor Excel Worksheet Functions 20 November 25th 06 02:26 PM
how do i count cells with values in a certain range ? Capt. Trevor Bailey Excel Discussion (Misc queries) 3 October 11th 06 03:48 PM
How do I define a range using values in cells? Matthieu de Wit Excel Worksheet Functions 2 April 26th 06 09:43 PM


All times are GMT +1. The time now is 10:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"