Count number of value repeated.

[lazylilsnoop]

Hi,

Type	Cost
Z	100
Z	100
Z,Y	200
Z,P	100
Z,Y,P	100

<tbody>
</tbody>

this is simplified version of what I'm trying to search, my list goes until 2000 cells

anyway, i need to figure out how many "Z" appears in TYPE
I used "countif(A2:A5, "Z") but it return the value 3.
if i want it to return 5, what should i input?

The other question is if i would like to sum of all the value that has Type Z in it, what is the formula?
"=sumif(A2:A5,"Z",B2:B5)" couldn't do the trick


thanks alot

 

[vogel997]

Try this

=countif(A1:A5, "Z*")

=SUMIF(A1:A5,"z*",B1:B5)

 

[lazylilsnoop]

Awesome! by adding the " * " at the back of the word really does the magic.

but what if I were to type Z in a cell,A20 (just so that i can search the whole sheet by changing the Type I need to search)?
e.g "=countif(A1:A5,A20)"

 

[vogel997]

=COUNTIF(A1:A5,A20&"*") this should work

 

[lazylilsnoop]

This formula totally works!!!

I added "*"& infront and back....
so everything works perfectly now!! thanks

 

[vogel997]

you're welcome