Counting multiple values in single cells

**guykescheep** · 07-08-2013, 11:35 AM

Hi All,

I am new to this forum and not that keep in Excel :-( So forgive me if I'm not using the correct syntax...

I have a sheet containing results of an online survey.
Several columns contain identical data that needs to be "manipulated".

Let's take one column: in a single cell you can have:
- empty
- single text like 3mm
- multi text comma separated like 3mm, 5mm, 7mm, 9mm, ...<increments of 2mm> ... ,35mm
There are 120 rows FYI.

Per column I need to calculate the total amount of each occurance of 3mm, 5mm, 7mm, ... , 35mm

No clue how to tackle this. Working on Excel 2011 for Mac.

All help and tips are really appreciated.

Thanks!

**Craig K.** · 07-08-2013, 12:33 PM

Is it possible for you to upload a sample of the spreadsheet (with any sensitive date removed)? To upload, click "Go Advanced" then "Manage Attachments".

**guykescheep** · 07-08-2013, 12:47 PM

Hi Craig, thanks for your effort and time willing to have a look at this.
A cleaned file should be uploaded.

I put the expected totals in A92:A102, so the effective sums are to be for Column B, C, ...

Many thanks

**Jonmo1** · 07-08-2013, 12:56 PM

Assuming a single cell will not contain multiple instances of an item
ie..no single cell will contain 3mm twice.
Try this in B92 filled Down/Right as far as required

=COUNTIF(B$2:B$90,"*"&MID($A92,7,255)&"*")

**guykescheep** · 07-08-2013, 01:28 PM

Hi Jonmo,

Excellent! This works.

Many thanks my man, you saved my monday ;-)

**Jonmo1** · 07-08-2013, 01:35 PM

Glad to help, thanks for the feedback..

Originally Posted by guykescheep

Just the A92 needs to be without $ to fill down correctly.

No it doesn't, $A92 WILL incriment to $A93 as the formula is filled DOWN.
That $ locks the COLUMN reference (A), and prevents it from incrimenting to B when formula is filled ACCROSS.