Sum with condition

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

 

[Domenic]

Try...

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

or

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

Note, however, SUMIF should be faster.

 

[Bernard Liengme]

=SUMPRODUCT(--(isnumber(F21:F33)),C21:C31)
untested (getting late) but should work
best wishes

 

[Khalil Handal]

Thanks to both of you, it worked just fine.

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