A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SUMIFS



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 03:38 PM posted to microsoft.public.excel.misc
Rak
external usenet poster
 
Posts: 12
Default SUMIFS

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC

  #2  
Old June 4th, 2010, 03:57 PM posted to microsoft.public.excel.misc
steve
external usenet poster
 
Posts: 28
Default SUMIFS

That formula works for me, is your column AN formated as text

Regards
Steve
"RAK" wrote in message
...
My SUMIFS formula was "accepted" but the result is 0.0 even though there
are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is
EOC



  #3  
Old June 4th, 2010, 04:07 PM posted to microsoft.public.excel.misc
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 9
Default SUMIFS

On Fri, 4 Jun 2010 07:38:33 -0700, RAK
wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC


It is quite possible that the values in AN3:AN997 are text and not
numbers.

How are those values generated?

You can use the ISNUMBER (or ISTEXT) commands to see how Excel is
interpreting these values.

If they are being generated by a formula, you'll need to ensure the
formula is outputing numbers and not text.

If they were input manually, or copied from some other source, you
will need to convert them to numbers. This can be done easily,
depending on how the values have been entered.
  #4  
Old June 4th, 2010, 04:11 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default SUMIFS

Maybe the value in AN3:An997 aren't really numbers. Maybe they're just text
that looks like numbers.

If you put:
=count(an3:an997)
in a cell
and
=counta(an3:an997)
do those evaluate to the same result?

And a silly suggestion...

Are you sure you're using the correct columns?

RAK wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC


--

Dave Peterson
  #5  
Old June 4th, 2010, 04:52 PM posted to microsoft.public.excel.misc
Ms-Exl-Learner
external usenet poster
 
Posts: 522
Default SUMIFS

1) Check whether the AN3:AN997 numbers are formatted as Text.
2) Check whether AN3:AN997 numbers are entered with a leading single quote.
3) Check whether C3:C997 range is having the Value “ARC” with any leading or
preceding spaces.
4) Check whether D3997 range is having the Value “EOC” with any leading or
preceding spaces.

If all the above is perfect then the formula will run perfectly.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"RAK" wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC

  #6  
Old June 4th, 2010, 05:47 PM posted to microsoft.public.excel.misc
Rak
external usenet poster
 
Posts: 12
Default SUMIFS



"RAK" wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC
AN is formatted as numbers (format cellNumberNumberpositive 1234.0 w/o ( )

  #7  
Old June 4th, 2010, 06:03 PM posted to microsoft.public.excel.misc
Rak
external usenet poster
 
Posts: 12
Default SUMIFS



"Ron Rosenfeld" wrote:

On Fri, 4 Jun 2010 07:38:33 -0700, RAK
wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC


It is quite possible that the values in AN3:AN997 are text and not
numbers.

How are those values generated?

You can use the ISNUMBER (or ISTEXT) commands to see how Excel is
interpreting these values.

If they are being generated by a formula, you'll need to ensure the
formula is outputing numbers and not text.

If they were input manually, or copied from some other source, you
will need to convert them to numbers. This can be done easily,
depending on how the values have been entered.
.
ISNUMBER shows true that it is a number - it is generated by a formula

  #8  
Old June 4th, 2010, 06:03 PM posted to microsoft.public.excel.misc
Rak
external usenet poster
 
Posts: 12
Default SUMIFS



"Dave Peterson" wrote:

Maybe the value in AN3:An997 aren't really numbers. Maybe they're just text
that looks like numbers.

If you put:
=count(an3:an997)
in a cell
and
=counta(an3:an997)
do those evaluate to the same result?

And a silly suggestion...

Are you sure you're using the correct columns?

RAK wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC


--

Dave Peterson
.
They appear to be numbers by cell format screen and return of true value fromISNUMBER

  #9  
Old June 4th, 2010, 06:05 PM posted to microsoft.public.excel.misc
Rak
external usenet poster
 
Posts: 12
Default SUMIFS



"Ms-Exl-Learner" wrote:

1) Check whether the AN3:AN997 numbers are formatted as Text.
2) Check whether AN3:AN997 numbers are entered with a leading single quote.
3) Check whether C3:C997 range is having the Value “ARC” with any leading or
preceding spaces.
4) Check whether D3997 range is having the Value “EOC” with any leading or
preceding spaces.

If all the above is perfect then the formula will run perfectly.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"RAK" wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC
your conditions 1,3 & 4 are okay. Can you expand on condition 2 - single quote

  #10  
Old June 4th, 2010, 07:49 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default SUMIFS

Then check the values in the other fields. Maybe they're not what you're
looking for.

RAK wrote:

"Dave Peterson" wrote:

Maybe the value in AN3:An997 aren't really numbers. Maybe they're just text
that looks like numbers.

If you put:
=count(an3:an997)
in a cell
and
=counta(an3:an997)
do those evaluate to the same result?

And a silly suggestion...

Are you sure you're using the correct columns?

RAK wrote:

My SUMIFS formula was "accepted" but the result is 0.0 even though there are
numbers to add. What can the problem be?
The formula is =SUMIFS(AN3:AN997,C3:C997,"ARC",D3997,"EOC")
I want the total form column AN when column C is ARC and when column D is EOC


--

Dave Peterson
.
They appear to be numbers by cell format screen and return of true value fromISNUMBER


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:55 PM.


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