Sumproduct, but errors in column

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

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(ISERROR('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
 
Try this array formula

=SUM(IF((NOT(ISERROR(accts!B$1:B$10)))*(accts!A$1:A$10="Mary"),accts!B$1:B$10))
 
Here's another one...

Array entered** :

=SUM(IF('accts'!A$1:A$1000="Mary",IF(ISNUMBER('accts'!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.
 
ker_01 said:
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.
Click to expand...

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 -----
 
PS....
One way: the following array formula [*]:
=SUM(IF(ISNUMBER('accts'!B1:B5),
('accts'!A1:A5="mary")*'accts'!B1:B5))
Click to expand...

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 said:
ker_01 said:
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.
Click to expand...

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 said:
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(ISERROR('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
Click to expand...
Click to expand...
 
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))),(accts!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
 
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)))*(accts!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
 
Back
Top