Sum on 2 criteria, incl #REF!
 

Wasn't sure how to title this one.... ;)

Here's the situation (XL2002 on XP):
Data to be summed is in cells E8:BR8 and is linked to external WBs --
some cells have #REF! because the linked WB doesn't exist yet. Cells
E5:BR5 contain a category code ('N' or 'C'). I need to sum E8:BR8
based on the value cell E5:BR5.

For instance:
Row 5: N C N N C C
Row 8: 1 1 1 1 #REF! 2

Summing values in Row8 based on 'C' in Row5 should return '3'...

I've tried to modify this formula:
=SUM(IF(NOT(ISERROR(E5:E14)),E5:E14,0)) by adding an AND criteria, but
obviously it didn't work....

TIA,
Ray

Sum on 2 criteria, incl #REF!
 

Try this ARRAY FORMULA:

=SUMPRODUCT((E5:BR5="C")*IF(ISNUMBER(E8:BR8),E8:BR 8))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Wasn't sure how to title this one.... ;)

Here's the situation (XL2002 on XP):
Data to be summed is in cells E8:BR8 and is linked to external WBs --
some cells have #REF! because the linked WB doesn't exist yet. Cells
E5:BR5 contain a category code ('N' or 'C'). I need to sum E8:BR8
based on the value cell E5:BR5.

For instance:
Row 5: N C N N C C
Row 8: 1 1 1 1 #REF! 2

Summing values in Row8 based on 'C' in Row5 should return '3'...

I've tried to modify this formula:
=SUM(IF(NOT(ISERROR(E5:E14)),E5:E14,0)) by adding an AND criteria, but
obviously it didn't work....

TIA,
Ray

Sum on 2 criteria, incl #REF!
 

Easiest thing I can think of is for you to add a helper row which tests for
errors, and, if they exist, replaces those errors with a 0 value.

Assuming your example below is in row 1, then in row 2 I would put the
following formula (starting in cell A2): =IF(ISERROR(A1),0,A1) and fill to
the right. Then sum.

Dave
--
Brevity is the soul of wit.


"Ray" wrote:

Wasn't sure how to title this one.... ;)

Here's the situation (XL2002 on XP):
Data to be summed is in cells E8:BR8 and is linked to external WBs --
some cells have #REF! because the linked WB doesn't exist yet. Cells
E5:BR5 contain a category code ('N' or 'C'). I need to sum E8:BR8
based on the value cell E5:BR5.

For instance:
Row 5: N C N N C C
Row 8: 1 1 1 1 #REF! 2

Summing values in Row8 based on 'C' in Row5 should return '3'...

I've tried to modify this formula:
=SUM(IF(NOT(ISERROR(E5:E14)),E5:E14,0)) by adding an AND criteria, but
obviously it didn't work....

TIA,
Ray

Sum on 2 criteria, incl #REF!
 

Ray,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(ISERROR(E8:BR8),0,E8:BR8)*(E5:BR5="C"))

HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
ups.com...
Wasn't sure how to title this one.... ;)

Here's the situation (XL2002 on XP):
Data to be summed is in cells E8:BR8 and is linked to external WBs --
some cells have #REF! because the linked WB doesn't exist yet. Cells
E5:BR5 contain a category code ('N' or 'C'). I need to sum E8:BR8
based on the value cell E5:BR5.

For instance:
Row 5: N C N N C C
Row 8: 1 1 1 1 #REF! 2

Summing values in Row8 based on 'C' in Row5 should return '3'...

I've tried to modify this formula:
=SUM(IF(NOT(ISERROR(E5:E14)),E5:E14,0)) by adding an AND criteria, but
obviously it didn't work....

TIA,
Ray

Sum on 2 criteria, incl #REF!
 

Actually, you don't really need to use SUMPRODUCT....

=SUM((E5:BR5="C")*IF(ISNUMBER(E8:BR8),E8:BR8))

Note: That's still an ARRAY FORMULA, so [Ctrl] [Shift] [Enter]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=SUMPRODUCT((E5:BR5="C")*IF(ISNUMBER(E8:BR8),E8:BR 8))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Wasn't sure how to title this one.... ;)

Here's the situation (XL2002 on XP):
Data to be summed is in cells E8:BR8 and is linked to external WBs --
some cells have #REF! because the linked WB doesn't exist yet. Cells
E5:BR5 contain a category code ('N' or 'C'). I need to sum E8:BR8
based on the value cell E5:BR5.

For instance:
Row 5: N C N N C C
Row 8: 1 1 1 1 #REF! 2

Summing values in Row8 based on 'C' in Row5 should return '3'...

I've tried to modify this formula:
=SUM(IF(NOT(ISERROR(E5:E14)),E5:E14,0)) by adding an AND criteria, but
obviously it didn't work....

TIA,
Ray

Sum on 2 criteria, incl #REF!
 

Thanks for all of your responses...

Dave - your solution would probably work, but I actually have about
30rows of data to Sum (left this piece out to keep things simple).

Ron/Bernie -- both of yours worked great!

Thanks, ray


Bernie Deitrick wrote:
Ray,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(ISERROR(E8:BR8),0,E8:BR8)*(E5:BR5="C"))

HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
ups.com...
Wasn't sure how to title this one.... ;)

Here's the situation (XL2002 on XP):
Data to be summed is in cells E8:BR8 and is linked to external WBs --
some cells have #REF! because the linked WB doesn't exist yet. Cells
E5:BR5 contain a category code ('N' or 'C'). I need to sum E8:BR8
based on the value cell E5:BR5.

For instance:
Row 5: N C N N C C
Row 8: 1 1 1 1 #REF! 2

Summing values in Row8 based on 'C' in Row5 should return '3'...

I've tried to modify this formula:
=SUM(IF(NOT(ISERROR(E5:E14)),E5:E14,0)) by adding an AND criteria, but
obviously it didn't work....

TIA,
Ray