Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|