Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"EOC") I want the total form column AN when column C is ARC and when column D is EOC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"EOC") I want the total form column AN when column C is ARC and when column D is EOC |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"EOC") I want the total form column AN when column C is ARC and when column D is EOC -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 D3:D997 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",D3:D997,"EOC") I want the total form column AN when column C is ARC and when column D is EOC |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 D3:D997 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",D3:D997,"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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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",D3:D997,"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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIFS
On Fri, 4 Jun 2010 10:03:09 -0700, RAK
wrote: ISNUMBER shows true that it is a number - it is generated by a formula Please post the formula. Also, if it is truly a number, then your problem is that your value in the SUMIFS criteria, and the value in the SUMIFS criteria range, are not exactly the same. There may be extra spaces, or nbsp's or other contents difficult to see. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use sumifs - | Excel Worksheet Functions | |||
SUMIFS help | Excel Worksheet Functions | |||
SUMIFS | Excel Worksheet Functions | |||
SUMIFS | Excel Discussion (Misc queries) | |||
SUMIFS and OR | Excel Worksheet Functions |