Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 17th 10, 02:43 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 10
Default Attachment :SUM of a colum where IF function is used

Hi,

I would like to have the sum of cloumn 'C' in the attached file.
An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.
Please help me.

Jeevan





  #2   Report Post  
Old April 17th 10, 03:58 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,203
Default Attachment :SUM of a colum where IF function is used

I cannot see your attachment here.
Do you have any error indications in C1:C21? Such as #DIV/0 or #NA, #VALUE
or others beginning with #?
Those will prevent the SUM() from returning the sum. You would have to
correct or eliminate such errors before the SUM() will return the correct
result.

IF you do have such error indications, please show us the formula being used
in the cell and tell us what the error indication is. We may be able to help
rewrite the formula so that your SUM() works properly.

Probably a good idea to simply show us an example of the IF() statements
anyhow.

"Vinay Vasu" wrote:

Hi,

I would like to have the sum of cloumn 'C' in the attached file.
An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.
Please help me.

Jeevan



  #3   Report Post  
Old April 17th 10, 05:07 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default Attachment :SUM of a colum where IF function is used

Take out the "1", etc so you will have NUMBERS

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vinay Vasu" wrote in message
...
Hi,

I would like to have the sum of cloumn 'C' in the attached file.
An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.
Please help me.

Jeevan




  #4   Report Post  
Old April 17th 10, 05:14 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 905
Default Attachment :SUM of a colum where IF function is used

"Vinay Vasu" wrote:
I would like to have the sum of cloumn 'C' in the
attached file.


Many people cannot see an attachment because of the news interface that they
use. This includes users of Google Groups and the MS Discussion Groups web
interface.

Your worksheet is simple enough to describe.


An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.


In column C, you have formulas of the form:

=IF(B2=A2,"1",IF(B2=0,"0",IF(B2<A2,"-0.25")))

The root cause of your problem is: the IF expression should return numbers
(1, 0, -0.25), not text ("1", "0", "-0.25"). You should write:

=IF(B2=A2,1,IF(B2=0,0,IF(B2<A2,-0.25)))

Note: The formula can be simplified to:

=IF(B2=A2,1,IF(B2=0,0,-0.25))

Also, when A2 is zero, does that give you the intended result: 1, not 0?

I wonder if you really want:

=IF(B2=0,0,IF(B2=A2,1,-0.25))


----- original message -----

"Vinay Vasu" wrote in message
...
Hi,

I would like to have the sum of cloumn 'C' in the attached file.
An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.
Please help me.

Jeevan




  #5   Report Post  
Old April 18th 10, 05:43 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 10
Default Attachment :SUM of a colum where IF function is used

Thanks a lot.

It worked well.

Jeevan

"Joe User" <joeu2004 wrote in message
...
"Vinay Vasu" wrote:
I would like to have the sum of cloumn 'C' in the
attached file.


Many people cannot see an attachment because of the news interface that
they use. This includes users of Google Groups and the MS Discussion
Groups web interface.

Your worksheet is simple enough to describe.


An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.


In column C, you have formulas of the form:

=IF(B2=A2,"1",IF(B2=0,"0",IF(B2<A2,"-0.25")))

The root cause of your problem is: the IF expression should return
numbers (1, 0, -0.25), not text ("1", "0", "-0.25"). You should write:

=IF(B2=A2,1,IF(B2=0,0,IF(B2<A2,-0.25)))

Note: The formula can be simplified to:

=IF(B2=A2,1,IF(B2=0,0,-0.25))

Also, when A2 is zero, does that give you the intended result: 1, not 0?

I wonder if you really want:

=IF(B2=0,0,IF(B2=A2,1,-0.25))


----- original message -----

"Vinay Vasu" wrote in message
...
Hi,

I would like to have the sum of cloumn 'C' in the attached file.
An IF function is used to derive column C.
=SUM ( C1:C21) is not giving the result.
Please help me.

Jeevan








  #6   Report Post  
Old July 5th 21, 12:14 PM
Member
 
First recorded activity by ExcelBanter: Jun 2021
Posts: 47
Default Sumif

Hello! I think it would be so much easier to use the SUMIF Function in excel for a conditional sum of values.

The way to use SUMIF is:

=SUMIF(range, criteria, sum_range)

range - range of where you will check the critiera
criteria - the condition where you will check whether to add the value
sum range - the range to be summed based on the condition


So it will easily add all values based on the condition you set. Hope this helps!


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 of a colum where IF function is used Vinay Vasu New Users to Excel 1 April 17th 10 05:04 PM
Different colum widths for same colum in different areas of worksh Paul EWM Aventura Excel Worksheet Functions 6 March 24th 08 02:54 PM
colum 1 = product #'s than colum 2 = prod. disc. newriver Excel Discussion (Misc queries) 2 January 21st 06 10:53 PM
Keeping a sum colum correct after inserting a colum of data in fro hazel Excel Discussion (Misc queries) 3 October 19th 05 09:51 PM
Check data on colum A and find match on colum b Chris(new user) Excel Discussion (Misc queries) 3 March 20th 05 05:45 PM


All times are GMT +1. The time now is 03:09 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017