Sum on 2 criteria, incl #REF!

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

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

Array enter (enter using Ctrl-Shift-Enter)

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

HTH,
Bernie
MS Excel MVP
 
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
 
Back
Top