Counting the same like data in on cell

[Alaska1]

I want to be able to count the number of times something shows up in cell. Would I use a countif function? I have attached a sample copy. In K2 it has the word Video in it. I want to be able to count video for that cell twice.

[benishiryo]

hi Alaska1. try:
=(LEN([@Source])-LEN(SUBSTITUTE([@Source],"Video","")))/LEN("video")

basically it means
(length of original text minus length of original text without the word "video") divided by length of the word "video"
so for K2 would be
=(12-2)/5
=2

[Alaska1]

Thank you for your reply. It did not work for me.

[FDibbins]

It worked for me? It gave 2 for K2 and K46.

What was your expected results?

[Alaska1]

When I try to cut and paste the formula it is not work...it does not recognize @source.

Thank you for your help.

[FDibbins]

So create the formula for yourself...
=(LEN(click-the-cell-ref)-LEN(SUBSTITUTE(click-the-cell-ref,"Video","")))/LEN("video")
If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Alaska1]

I have attached a copy and cleaned it up. The formula is on the report tab. It is counting two video but there are four in
that field. The Data is in a Table.

Thank you for your help.

[FDibbins]

Copy the formula to L2 on Sheet1, not Report. I have changed it a little, it is the same formula, but I used a reference, insteasd of hard coding...
=(LEN([@Source])-LEN(SUBSTITUTE([@Source],report!$C$2,"")))/LEN(report!$C$2)

	J	K	L
1	Theme3	Source	Column1
2		Video Video	2
3			0
4		Profile Slide	0

Then on Report, use this...
=SUM(Sheet1!L:L)

[FDibbins]

Had another look at this. If you just want to show the answer on Report and not do the calc in sheet1 for each row, put this in D3 in report...
=SUMPRODUCT((LEN(Sheet1!$K2:K266)-LEN(SUBSTITUTE(Sheet1!$K2:K266,report!$C$2,"")))/LEN(report!$C$2))

[Alaska1]

Thank you again for all your help. I tried the formula and it gave me 4. The only thing is the data is in a Table and I need it to be dynamic...which I can use the Table name.

But I need it to read
Video as a total for the column not by row.

So a total at the bottom
Video 4
Slide 5

I need a total for those fields. As if I was doing a countifs function. But that only picks up the category once in the cell not twice.

[Alaska1]

Thank you for your help. I adjust the formula using sumproduct and it worked.