Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Sum Cell Values of one column based on Another Cell Value in a different column

Hi All!

I need to place the sum of certain cell values in a separate cell. Easy
enough, except for one thing:

Here's the deal. If F21 has "B" in it, that's when I need E21's value added.
If there is ANYTHING else in column F, don't add the value of the
corresponding E column cell. I would like a function formula that I will
place in E6 that will work for the entire columns E & F.

Let me know if you need additional information to understand my query.

Thank you!
Kristen

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Sum Cell Values of one column based on Another Cell Value in a different column

Hi Kristen,

You can use the SUMIF function to achieve this. Here are the steps:
  1. Select the cell where you want the sum to appear (E6 in this case).
  2. Type the following formula:
    Formula:
    =SUMIF(F:F,"B",E:E
  3. Press Enter.

This formula will add up all the values in column E where the corresponding cell in column F is "B". If the cell in column F is anything else, it will not be included in the sum.

You can copy and paste this formula to other cells in column E and it will work for the entire columns E and F.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 303
Default Sum Cell Values of one column based on Another Cell Value in a different column

=sumproduct(--(F1:F1000="B"),(E1:E1000))
sumproduct needs a defined column length. I selected 1000

or you could try =sumif(F1:F1000,"B",E1:E1000)


with a sumproduct formula you can have more than one condition

see.....http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Greetings from New Zealand

"kristenb via OfficeKB.com" <u35894@uwe wrote in message
news:7f3e15ea246dc@uwe...
Hi All!

I need to place the sum of certain cell values in a separate cell. Easy
enough, except for one thing:

Here's the deal. If F21 has "B" in it, that's when I need E21's value
added.
If there is ANYTHING else in column F, don't add the value of the
corresponding E column cell. I would like a function formula that I will
place in E6 that will work for the entire columns E & F.

Let me know if you need additional information to understand my query.

Thank you!
Kristen

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,942
Default Sum Cell Values of one column based on Another Cell Value in a dif

hi
sumif????
=sumif(F:F,"b",E:E)
but you can't put the formula in E6 else you would be a circular reference
error.
you could modify to something like
=sumif(F5:F500,"b",E5:E500) or what ever.

regards
FSt1


"kristenb via OfficeKB.com" wrote:

Hi All!

I need to place the sum of certain cell values in a separate cell. Easy
enough, except for one thing:

Here's the deal. If F21 has "B" in it, that's when I need E21's value added.
If there is ANYTHING else in column F, don't add the value of the
corresponding E column cell. I would like a function formula that I will
place in E6 that will work for the entire columns E & F.

Let me know if you need additional information to understand my query.

Thank you!
Kristen

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default Sum Cell Values of one column based on Another Cell Value in a dif

if you are limiting this to when the cell in column contains ONLY a B, then

=sumif(F7:F100,"B",E7:E100)

if you want to sum when column F BEGINS with B, then

=sumif(F7:F100,"B*",E7:E100)

and if you want to sum anytime column F contains a B anywhere in the cell

=sumif(F7:F100,"*B*",E7:E100)



"kristenb via OfficeKB.com" wrote:

Hi All!

I need to place the sum of certain cell values in a separate cell. Easy
enough, except for one thing:

Here's the deal. If F21 has "B" in it, that's when I need E21's value added.
If there is ANYTHING else in column F, don't add the value of the
corresponding E column cell. I would like a function formula that I will
place in E6 that will work for the entire columns E & F.

Let me know if you need additional information to understand my query.

Thank you!
Kristen

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Sum Cell Values of one column based on Another Cell Value in a different column

Thank you everyone! The =sumif that you all suggested worked perfectly! You
are all very helpful and thank you for the quick response!
Kristen



Bill Kuunders wrote:
=sumproduct(--(F1:F1000="B"),(E1:E1000))
sumproduct needs a defined column length. I selected 1000

or you could try =sumif(F1:F1000,"B",E1:E1000)

with a sumproduct formula you can have more than one condition

see.....http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Hi All!

[quoted text clipped - 11 lines]
Thank you!
Kristen


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1

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
SUM values in this column F only if cell is blank in column Q...HE Lisa Excel Discussion (Misc queries) 3 March 1st 07 04:22 PM
Count entries in one column based on values in another column Kurt Excel Worksheet Functions 7 January 10th 07 10:29 PM
conditional cell format based on cell in same row, previous column tamiluchi Excel Worksheet Functions 7 May 3rd 06 04:11 PM
Hide column or row based on a cell in the column or row? SacGuy Excel Discussion (Misc queries) 0 January 24th 06 07:51 PM
Format cell in column B based on value in the next cell (column c) Nicole Excel Discussion (Misc queries) 7 May 18th 05 10:19 PM


All times are GMT +1. The time now is 10:33 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"