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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct, but errors in column
I've been asked by a colleague to help tally some data, and sumproduct is the
logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith |
#2
|
|||
|
|||
Sumproduct, but errors in column
Try this array formula
=SUM(IF((NOT(ISERROR(accts!B$1:B$10)))*(accts!A$1: A$10="Mary"),accts!B$1:B$10)) -- HTH Bob "ker_01" wrote in message ... I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith |
#3
|
|||
|
|||
Sumproduct, but errors in column
Here's another one...
Array entered** : =SUM(IF('accts'!A$1:A$1000="Mary",IF(ISNUMBER('acc ts'!B$1:B$1000),'accts'!B$1:B$1000))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith |
#4
|
|||
|
|||
Sumproduct, but errors in column
"ker_01" wrote:
I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. One way: the following array formula[*]: =SUM(IF(ISNUMBER('accts'!B1:B5), ('accts'!A1:A5="mary")*'accts'!B1:B5)) [*] An array formula is entered by pressing ctrl+shift+Enter, not just Enter. Excel will display the formula enclosed in curly braces in the Formula Bar, e.g. {=formula}. You cannot enter the curly braces yourself. If you make a mistake, select the cell, press F2, edit as needed, then press ctrl+shift+Enter. ----- original message ----- "ker_01" wrote: I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith |
#5
|
|||
|
|||
Sumproduct, but errors in column
PS....
I wrote: One way: the following array formula[*]: =SUM(IF(ISNUMBER('accts'!B1:B5), ('accts'!A1:A5="mary")*'accts'!B1:B5)) Of course, the best solution is to eliminate the #N/A error (or N/A# text, as your wrote it) in the first place. It makes for a messy worksheet anyway. Post again here if you would like help in avoiding the #N/A error. ----- original message ----- "Joe User" wrote: "ker_01" wrote: I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. One way: the following array formula[*]: =SUM(IF(ISNUMBER('accts'!B1:B5), ('accts'!A1:A5="mary")*'accts'!B1:B5)) [*] An array formula is entered by pressing ctrl+shift+Enter, not just Enter. Excel will display the formula enclosed in curly braces in the Formula Bar, e.g. {=formula}. You cannot enter the curly braces yourself. If you make a mistake, select the cell, press F2, edit as needed, then press ctrl+shift+Enter. ----- original message ----- "ker_01" wrote: I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith |
#6
|
|||
|
|||
Sumproduct, but errors in column
First and foremost, thank you to all who responded. The array formula
approach worked fine, I just got 'locked in' on the idea that sumproduct should have worked. Can anyone provide a technical reason as to why it wouldn't? I even tried permutations including an IF statement, to avoid ever processing an error: =Sumproduct((IF(NOT(ISERROR(accts!B$1:B$10))),(acc ts!B$1:B$10),0)*1,(accts!A$1:A$10="Mary")*1) but I couldn't get that to work. I appreciate any additional info about sumproduct, as it may help me avoid other limitations of sumproduct in the future. Thanks! Keith "Bob Phillips" wrote: Try this array formula =SUM(IF((NOT(ISERROR(accts!B$1:B$10)))*(accts!A$1: A$10="Mary"),accts!B$1:B$10)) -- HTH Bob "ker_01" wrote in message ... I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith . |
#7
|
|||
|
|||
Sumproduct, but errors in column
It doesn't work because even though you have a condition to ignore errors,
trying to use the error-ridden range as a separate argument in SP just brings them all back in again. The IF version creates an array without the error cells because by saying IF(NOT(ISERROR(rng)),rng) you change the array from an array with errors ({1;2;3;#N/A}) to one without errors ({1;2;3;FALSE}), and it is the new array that gets processed further, unlike SP which processes the same array again. You could actually use SP =SUMPRODUCT(IF((NOT(ISERROR(accts!B$1:B$10)))*(acc ts!A$1:A$10="Mary"),accts!B$1:B$10)) but as you would have to array-enter it t make it work it is totally pointless to my mind. HTH Bob "ker_01" wrote in message ... First and foremost, thank you to all who responded. The array formula approach worked fine, I just got 'locked in' on the idea that sumproduct should have worked. Can anyone provide a technical reason as to why it wouldn't? I even tried permutations including an IF statement, to avoid ever processing an error: =Sumproduct((IF(NOT(ISERROR(accts!B$1:B$10))),(acc ts!B$1:B$10),0)*1,(accts!A$1:A$10="Mary")*1) but I couldn't get that to work. I appreciate any additional info about sumproduct, as it may help me avoid other limitations of sumproduct in the future. Thanks! Keith "Bob Phillips" wrote: Try this array formula =SUM(IF((NOT(ISERROR(accts!B$1:B$10)))*(accts!A$1: A$10="Mary"),accts!B$1:B$10)) -- HTH Bob "ker_01" wrote in message ... I've been asked by a colleague to help tally some data, and sumproduct is the logical formula to use. However, the raw data has some N/A# errors in it, and I haven't figured out how to work around it. Col A Col B Joe $422 Mary $518 Joe $496 Mary $476 Mary N/A# So for my sumproduct, I'm limiting it by name, only taking rows that do not have an error value, and tallying the remaining values. =SUMPRODUCT(('accts'!A$1:A$1000="Mary")*1,(NOT(ISE RROR('accts'!B$1:B$1000)))*1,('accts'!B$1:B$1000)) If I remove the final condition, I do get a count of the number of rows for the target individual where there is no error value. However, when I include the last condition to get the actual sum of values, I think it is hitting those error values anyway and crashing the formula- it returns a N/A error for the sumproduct results. I thought that the second condition would be sufficient to limit my results to only the rows without errors, but perhaps an error in any of the conditions kills the whole evaluation, even if that row would have been omitted by another condition? Anyway, how does one tally a column with error values using sumproduct? Thank you! Keith . |
Thread Tools | |
Display Modes | |
|
|