countif and sum if over a range.
 

Hello.

I was wondering if someone can help me with the countif and sum if
functions. I am trying to count how many entries contain the same first four
characters as another entry. So I want to do something like:

sum(if (left(c2:c500,4) = left(T2,4), 1, 0)

Obviously this doesn't work. Is there some way to look at the first four
chars of a range of cells? Is there some way to maybe use the find function
over a range?

Also, it gives a #value error if I just do:

sum( if (c2:c500 = left(T2,4), 1, 0)

How do I avoid this error?

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

countif and sum if over a range.
 

Hi,

Try one of the following formulas depending on what you want,

=SUMPRODUCT(--(LEFT(C2:C500,4)=T2))
=SUMPRODUCT(--(LEFT(C2:C500,4)=LEFT(T2,4)))

Regards,
B. R. Ramachandran

"Michael" wrote:

Hello.

I was wondering if someone can help me with the countif and sum if
functions. I am trying to count how many entries contain the same first four
characters as another entry. So I want to do something like:

sum(if (left(c2:c500,4) = left(T2,4), 1, 0)

Obviously this doesn't work. Is there some way to look at the first four
chars of a range of cells? Is there some way to maybe use the find function
over a range?

Also, it gives a #value error if I just do:

sum( if (c2:c500 = left(T2,4), 1, 0)

How do I avoid this error?

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

countif and sum if over a range.
 

Great! Thank you so much for your help!

"B. R.Ramachandran" wrote:

Hi,

Try one of the following formulas depending on what you want,

=SUMPRODUCT(--(LEFT(C2:C500,4)=T2))
=SUMPRODUCT(--(LEFT(C2:C500,4)=LEFT(T2,4)))

Regards,
B. R. Ramachandran

"Michael" wrote:

Hello.

I was wondering if someone can help me with the countif and sum if
functions. I am trying to count how many entries contain the same first four
characters as another entry. So I want to do something like:

sum(if (left(c2:c500,4) = left(T2,4), 1, 0)

Obviously this doesn't work. Is there some way to look at the first four
chars of a range of cells? Is there some way to maybe use the find function
over a range?

Also, it gives a #value error if I just do:

sum( if (c2:c500 = left(T2,4), 1, 0)

How do I avoid this error?

I greatly appreciate any assistance you could provide.

Thanks,

-Michael