Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
oldgrayelf
 
Posts: n/a
Default How sum values in column B using values in column A as the conditi

I want to sum the values in column B using indicators in column A as the
condition. For instance, all values in column B matching the "1" indicator
in column A should be summed, and no others. Other indicators in column A
are 2, 3, etc. I know this takes an array formula, but I don't remember how
to do it.
  #2   Report Post  
Mac
 
Posts: n/a
Default

OGE:

If you are testing for a single condition in Column A, you can use the
SUMIF() function. The online help provides full documentation.
Alternatively, begin the formula, "=sumif(" and then press CTRL-A. Follow
the prompts.

=Mac=

"oldgrayelf" wrote in message
...
I want to sum the values in column B using indicators in column A as the
condition. For instance, all values in column B matching the "1"
indicator
in column A should be summed, and no others. Other indicators in column A
are 2, 3, etc. I know this takes an array formula, but I don't remember
how
to do it.



  #3   Report Post  
JulieD
 
Posts: n/a
Default

Hi

no need for an array formula you can use the SUMIF function
=SUMIF(A1:A10,1,B1:B10)
or with the 1 in C1, in D1
=SUMIF($A$1:$A$10,C1,$B$1:$B$10)
this can then be filled down the other 'indicators'

Cheers
JulieD

"oldgrayelf" wrote in message
...
I want to sum the values in column B using indicators in column A as the
condition. For instance, all values in column B matching the "1"
indicator
in column A should be summed, and no others. Other indicators in column A
are 2, 3, etc. I know this takes an array formula, but I don't remember
how
to do it.



  #4   Report Post  
oldgrayelf
 
Posts: n/a
Default

SUMIF will add the ENTIRE "B" column if there is ANY "1" in column "A". I
only want the values in column "B" added that match the "1" identifier in
column "A". I did remember how to do it. Use this array formula (enter it
using Crtl-Shift-Enter after entering the formula into the cell you want the
answer to be found): "{=SUM(IF(A1:A10="1",B1:B10))}" Do not type in the
curly brackets. Excel adds them when you enter the formula using the
Crtl-Shift-Enter combination to identify it as an array formula and not a
regular formula. If you just enter the formula using the Enter key, you will
get an error. If there is another identifier, say "2", in column "A" and you
want those values that match it in column "B" added, the array formula looks
like this: {=SUM(IF(A1:A10="2",B1:B10))} - and so on.

"JulieD" wrote:

Hi

no need for an array formula you can use the SUMIF function
=SUMIF(A1:A10,1,B1:B10)
or with the 1 in C1, in D1
=SUMIF($A$1:$A$10,C1,$B$1:$B$10)
this can then be filled down the other 'indicators'

Cheers
JulieD

"oldgrayelf" wrote in message
...
I want to sum the values in column B using indicators in column A as the
condition. For instance, all values in column B matching the "1"
indicator
in column A should be summed, and no others. Other indicators in column A
are 2, 3, etc. I know this takes an array formula, but I don't remember
how
to do it.




  #5   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

No, sumif will NOT add the entire range, only those that has the 1 criteria
in A
although the array formula works it's a waste of resources compared to SUMIF
which is faster
and simpler


Regards,

Peo Sjoblom

"oldgrayelf" wrote in message
...
SUMIF will add the ENTIRE "B" column if there is ANY "1" in column "A". I
only want the values in column "B" added that match the "1" identifier in
column "A". I did remember how to do it. Use this array formula (enter

it
using Crtl-Shift-Enter after entering the formula into the cell you want

the
answer to be found): "{=SUM(IF(A1:A10="1",B1:B10))}" Do not type in the
curly brackets. Excel adds them when you enter the formula using the
Crtl-Shift-Enter combination to identify it as an array formula and not a
regular formula. If you just enter the formula using the Enter key, you

will
get an error. If there is another identifier, say "2", in column "A" and

you
want those values that match it in column "B" added, the array formula

looks
like this: {=SUM(IF(A1:A10="2",B1:B10))} - and so on.

"JulieD" wrote:

Hi

no need for an array formula you can use the SUMIF function
=SUMIF(A1:A10,1,B1:B10)
or with the 1 in C1, in D1
=SUMIF($A$1:$A$10,C1,$B$1:$B$10)
this can then be filled down the other 'indicators'

Cheers
JulieD

"oldgrayelf" wrote in message
...
I want to sum the values in column B using indicators in column A as

the
condition. For instance, all values in column B matching the "1"
indicator
in column A should be summed, and no others. Other indicators in

column A
are 2, 3, etc. I know this takes an array formula, but I don't

remember
how
to do it.








  #6   Report Post  
oldgrayelf
 
Posts: n/a
Default

You are correct. I was wrong - and also entering the SUMIF function
incorrectly. Thank You!

"Peo Sjoblom" wrote:

No, sumif will NOT add the entire range, only those that has the 1 criteria
in A
although the array formula works it's a waste of resources compared to SUMIF
which is faster
and simpler


Regards,

Peo Sjoblom

"oldgrayelf" wrote in message
...
SUMIF will add the ENTIRE "B" column if there is ANY "1" in column "A". I
only want the values in column "B" added that match the "1" identifier in
column "A". I did remember how to do it. Use this array formula (enter

it
using Crtl-Shift-Enter after entering the formula into the cell you want

the
answer to be found): "{=SUM(IF(A1:A10="1",B1:B10))}" Do not type in the
curly brackets. Excel adds them when you enter the formula using the
Crtl-Shift-Enter combination to identify it as an array formula and not a
regular formula. If you just enter the formula using the Enter key, you

will
get an error. If there is another identifier, say "2", in column "A" and

you
want those values that match it in column "B" added, the array formula

looks
like this: {=SUM(IF(A1:A10="2",B1:B10))} - and so on.

"JulieD" wrote:

Hi

no need for an array formula you can use the SUMIF function
=SUMIF(A1:A10,1,B1:B10)
or with the 1 in C1, in D1
=SUMIF($A$1:$A$10,C1,$B$1:$B$10)
this can then be filled down the other 'indicators'

Cheers
JulieD

"oldgrayelf" wrote in message
...
I want to sum the values in column B using indicators in column A as

the
condition. For instance, all values in column B matching the "1"
indicator
in column A should be summed, and no others. Other indicators in

column A
are 2, 3, etc. I know this takes an array formula, but I don't

remember
how
to do it.






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
Move column values w/o formula Greg Excel Worksheet Functions 1 February 1st 05 11:01 PM
Finding Unique Values in Column Kirk P. Excel Discussion (Misc queries) 1 January 25th 05 03:01 PM
Removing duplicate values in a column natan Excel Worksheet Functions 2 November 22nd 04 07:48 AM
Last several values in a column Mike K Excel Worksheet Functions 2 November 19th 04 01:13 PM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 03:09 PM


All times are GMT +1. The time now is 10:40 AM.

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"