Remember Me?

#1
April 17th 10, 02:43 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2010 Posts: 10
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.

Jeevan

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

Jeevan

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

Jeevan

#4
April 17th 10, 05:14 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 905
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.

Jeevan

#5
April 18th 10, 05:43 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2010 Posts: 10
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.

Jeevan

#6
July 5th 21, 12:14 PM
 Member First recorded activity by ExcelBanter: Jun 2021 Posts: 47
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!

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Vinay Vasu New Users to Excel 1 April 17th 10 05:04 PM Paul EWM Aventura Excel Worksheet Functions 6 March 24th 08 02:54 PM newriver Excel Discussion (Misc queries) 2 January 21st 06 10:53 PM hazel Excel Discussion (Misc queries) 3 October 19th 05 09:51 PM 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.