SUMPRODUCT formaula not working

[Yunus]

Hi,

I'm attempting to determine how many cells have the value less than or
equal to 26 in column A but also has 1 in it's adjacent row Column C

Whats wrong with this formula =SUMPRODUCT((A25:A850<=26)*(C25:C850=1))

Is it because Column A and C in itself are derived from Formualae, e.g
cells in Column A starting at A25 are = Sheet1!A25
cells in Column C starting at C25 are = MID(B25,5,1)
thanks

 

[Claus Busch]

Hi Yunus,

Am Sat, 10 Mar 2012 15:20:51 -0000 schrieb Yunus:

Whats wrong with this formula =SUMPRODUCT((A25:A850<=26)*(C25:C850=1))

MID is a text function. Your 1 is text formatted. Use
=--MID(B25,5,1)
to change the 1 to number and your SUMPRODUCT formula is working.

You can also use
=SUMPRODUCT((A25:A850<=26)*(C25:C850="1"))
but I prefer the first suggestion.


Regards
Claus Busch