|
sumif with NAs
Hi there,
I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
=SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100)
"George" wrote: Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
Joel,
This still gives me N/A as the output? I cut and pasted as exactly shown below. George "Joel" wrote: =SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100) "George" wrote: Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
Try this
=SUMPRODUCT(--($B$16:$B$100="Australia"),--($C$16:$C$100<"N/A"),($C$16:$C$100)) After pasting this press CTRL-SHIFT-ENTER. Are there entries like "XXAustraliaXX"? If yes, then the above will not work... "George" wrote: Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
I recommend that you fix the #N/A's (if possible).
Otherwise, you'll need to use an array formula** : =SUM(IF((ISNUMBER(SEARCH("Australia",B16:B100)))*( ISNUMBER(C16:C100)),C16:C100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "George" wrote in message ... Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
Perfect - thanks T Valko!
"T. Valko" wrote: I recommend that you fix the #N/A's (if possible). Otherwise, you'll need to use an array formula** : =SUM(IF((ISNUMBER(SEARCH("Australia",B16:B100)))*( ISNUMBER(C16:C100)),C16:C100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "George" wrote in message ... Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
SUMPRODUCT doesn't directly work with wildcards. You have to use something
like this: ISNUMBER(SEARCH("Australia",range)) Also, SUMPRODUCT will never** work when #N/A's (any error) are in a numeric array. **You could do something like this: =SUMPRODUCT(--(B1:B10="x"),IF(ISNUMBER(C1:C10),C1:C10)) But that would need to be array entered and if it ends up as an array formula you're better off just using an array formula like: =SUM(IF((B1:B10="x")*(ISNUMBER(C1:C10)),C1:C10)) -- Biff Microsoft Excel MVP "Joel" wrote in message ... =SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100) "George" wrote: Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
Hmmm...
Just thought of something... =SUMPRODUCT(--(B1:B10="x"),IF(ISNUMBER(C1:C10),C1:C10)) If you're using Excel 2007 that could be written as: =SUMPRODUCT(--(B1:B10="x"),IFERROR(C1:C10,0)) But it's still an array formula. However, I don't know if that'd be better than the array formula: =SUM(IF((B1:B10="x")*(ISNUMBER(C1:C10)),C1:C10)) I'd have to do some tests. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... SUMPRODUCT doesn't directly work with wildcards. You have to use something like this: ISNUMBER(SEARCH("Australia",range)) Also, SUMPRODUCT will never** work when #N/A's (any error) are in a numeric array. **You could do something like this: =SUMPRODUCT(--(B1:B10="x"),IF(ISNUMBER(C1:C10),C1:C10)) But that would need to be array entered and if it ends up as an array formula you're better off just using an array formula like: =SUM(IF((B1:B10="x")*(ISNUMBER(C1:C10)),C1:C10)) -- Biff Microsoft Excel MVP "Joel" wrote in message ... =SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100) "George" wrote: Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
sumif with NAs
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "George" wrote in message ... Perfect - thanks T Valko! "T. Valko" wrote: I recommend that you fix the #N/A's (if possible). Otherwise, you'll need to use an array formula** : =SUM(IF((ISNUMBER(SEARCH("Australia",B16:B100)))*( ISNUMBER(C16:C100)),C16:C100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "George" wrote in message ... Hi there, I have the following sumif function that reports an error as there are N/As in column C. Can you please help? =SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100) The function works fine when there are no NAs but doesn't get there when there are NAs in the data. Thanks George |
| All times are GMT +1. The time now is 10:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com