Search code examples
excel-formulaexcel-2007formula

Excel: Sum if the value is numeric and meet a relative criteria

     A    B        C
1    N1   4        3
2    N2   7  Blocked
3    N3   5        5
4    N4  12        0

I want to SUM the total of column "C" if

  1. it is numeric
  2. if it is less than column B

I want it to be something like this, but obviously it doesn't work:

=SUMPRODUCT((C1:C4<B1:B4)*IF(ISNUMBER(C1:C4),C1:C4,0)))

This works when there is no Text cells:

=SUMPRODUCT((C1:C4<B1:B4)*(C1:C4))

This does a count instead of a sum:

=SUMPRODUCT((C1:C4<B1:B4)*ISNUMBER(C1:C4))

Please help.

Solution

  • Aha! This is actually quite simple and straightforward after reading Excel SUMPRODUCT Function. Thanks to the author Dave Bruns!

    The formula is

    =SUMPRODUCT(--(C1:C4<B1:B4),--(ISNUMBER(C1:C4)),C1:C4)