Sumproduct, but errors in column

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
 
B

Bob Phillips

Try this array formula

=SUM(IF((NOT(ISERROR(accts!B$1:B$10)))*(accts!A$1:A$10="Mary"),accts!B$1:B$10))
 
T

T. Valko

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.
 
J

Joe User

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.

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 -----
 
J

Joe User

PS....
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 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.

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
 
K

ker_01

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
 
B

Bob Phillips

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top