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