Sum with condition

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

Khalil Handal

Hi,
Range F21:F33 has either numeric values copied from other sheet or non
numeric value.
Range C21:C33 has only numeric values copied from a different sheet or typed
in directly.
It looks like this:
Colomn C Colomn F
100 87
200 120
100 - -
100 Sam

I want to SUM only the number in Range C21:F33 that has correspondance
numbers in the range F21:F33.
In the preious example I need the sum of the first 2 numbers only 100+200 =
300
Can this be done?
 
Try...

=SUMIF(F21:F33,"<9.99999999999999E+307",C21:C33)

or

=SUMPRODUCT(--ISNUMBER(F21:F33),C21:C33)

Note, however, SUMIF should be faster.
 
=SUMPRODUCT(--(isnumber(F21:F33)),C21:C31)
untested (getting late) but should work
best wishes
 
Thanks to both of you, it worked just fine.

Domenic said:
Try...

=SUMIF(F21:F33,"<9.99999999999999E+307",C21:C33)

or

=SUMPRODUCT(--ISNUMBER(F21:F33),C21:C33)

Note, however, SUMIF should be faster.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
Click to expand...
 
Back
Top