Countif/Sumproduct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya

I've got a calculated range of 1 column. The cells in this column can hold various bits of text

I want to count how many of these cells have the first 5 characters identical to cell A2. How can I do this? The following don't seem to work (the 'range' works fine)

countif(range,Left(<don't know what to enter here>,5)=left(A2,5)
sumproduct(left(range,5)=left(A2,5

Any ideas

Thank
TT
 
Seems to work lovely... Thank you very much Frank

But could you please explain why/how? I don't understand the difference that the '-' symbols make

(I can't leave stuff alone until I fully understand it. Sorry!

Thank

T

----- Frank Kabel wrote: ----

Hi Ton
tr
=SUMPRODUCT(--(LEFT(range,5)=LEFT(A2,5))

--
Regard
Frank Kabe
Frankfurt, German

Tony T wrote
 
Hi Tony
no problem with that :-)
the part
(LEFT(range,5)=LEFT(A2,5))
returns a boolean value (TRUE or FALSE)
the -- part converts this (by a double multiplication with -1) to a
number . So TRUE=1 and FALSE=0

You could write this formula also as
=SUMPRODUCT(1*(LEFT(range,5)=LEFT(A2,5)))
or
=SUMPRODUCT(0+(LEFT(range,5)=LEFT(A2,5)))

You need a mathematical operation to convert booleans
 
(LEFT(range,5)=LEFT(A2,5)) returns an array of TRUE/FALSE values.
SUMPRODUCT requires numeric values. One unary minus operator (-) coerces
the TRUE/FALSE values to -1/0 (any math operator will do this). The
second unary minus operator converts -1 to +1 for the SUM.
 
Back
Top