#1
|
|||
|
|||
Counting multiple data in cell more than once
I have a data in a column that may be listed more than once. If the data is different in that cell the countif statement will pick it up. If the data is the same the countif statement is only picking it up once. For example if video is in the cell twice it is only picking it up once. Is there a formula will it will count all data regardless if it is in the cell twice or not. I have attached a sample. The first worksheet is where my formula is. The second worksheet has the data. |
#2
|
||||
|
||||
This should do it
Code:
=SUMPRODUCT(--(LEN(K6:K21)-LEN(SUBSTITUTE(UPPER(K6:K21);"VIDEO";"")))/LEN("Video"))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
The data is in a Table because I will be making updates to this document. If I use the code below will that work.
=SUMPRODUCT(--(LEN(Table1)-LEN(SUBSTITUTE(UPPER(Table1);"VIDEO";"")))/LEN("Video")) I will need to change it for the other categories in that field =SUMPRODUCT(--(LEN(Table1)-LEN(SUBSTITUTE(UPPER(Table1);"BTS";"")))/LEN("BTS")) Thank you for your help |
#4
|
|||
|
|||
Thank you for your help. I adjusted the formula using sumproduct and it worked.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extracting data from a cell with multiple lines of text | MMT | Excel | 9 | 02-12-2015 09:18 PM |
Looking for VBA or Macro Counting a specific value occuring in single, random, changing cell? | blackjack | Excel Programming | 1 | 09-11-2014 11:27 PM |
Multiple data in one cell? | New Daddy | Excel | 4 | 09-06-2013 02:50 AM |
Counting cells with multiple complex criteria | TishyMouse | Excel | 12 | 12-06-2012 05:05 AM |
various data needs counting | apples | Excel | 2 | 09-16-2012 04:52 AM |