# Help with summarizing multiple cells

#### PhilG

##### New Member
Hello all,

I'm hoping that this is a simple solution, and I'm just not seeing it.

I have a worksheet with multiple client rows, with an adjacent date column (it's a schedule). What I need to do, is have a formula (or VBA code) that looks through the date column and finds all client rows with a given value (ie. same date), and summarize it all in one cell.

For instance ...

A2 = ABC Co.
A3 = XYZ Co.
A4 = John Doe

B2 = 01/01/2005
B3 = 01/12/2005
B4 = 01/01/2005

Here's what I need calculated from these ...

In C2, I need to display all clients with a date of 01/01/2005 (ie. ABC Co. and John Doe).

I have been able to do an OFFSET & MATCH combo to get me close

[ =OFFSET(\$A\$1,MATCH("01/01/2005",\$B\$2:\$B\$4,0),0) ]

but that only gives me the first instance of 01/01/2005 (ie. ABC Co.). How do I get this to look through the entire list and list ALL clients scheduled on 01/01/2005?

Or would this require VBA code?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Not entirely ...

It's true that Autofilter would hide the non-matching rows, and leave me with multiple rows of clients with a matching date.

However, what I must have is the matching data to be shown in 1 cell (it's a long story).

Based on my example above, C2 must result in "ABC Co. John Doe" -- I can worry about the formatting latter once I get the values I need in there.

AutoFilter doesn't help me in this case, but thanks for the prompt reply Aladin.

how many client records would you have in your list ?
how many would you expect in 1 day ?

The # of client rows ranges from 70 to 300.
The # of times a given date is listed will range from 1 to 10

PhilG said:
Not entirely ...

It's true that Autofilter would hide the non-matching rows, and leave me with multiple rows of clients with a matching date.

However, what I must have is the matching data to be shown in 1 cell (it's a long story).

Based on my example above, C2 must result in "ABC Co. John Doe" -- I can worry about the formatting latter once I get the values I need in there.

AutoFilter doesn't help me in this case, but thanks for the prompt reply Aladin.

Book1
BCDEFG
1
2ABC Co.1/1/2005
3XYZ Co.1/12/20051/1/2005ABC Co.,John Doe
4John Doe1/1/2005
5
6
Sheet1

G3:

=SUBSTITUTE(MCONCAT(IF(\$C\$2:\$C\$4=F3,","&\$B\$2:\$B\$4,"")),",","",1)

which must be confirmed with control+shift+enter, not just with enter.

Note that MCONCAT has a limit of 255 chars.

That looks like it will do the trick.

Got it ... and it works wonderfully!

Thanks much for the help. :D

Replies
11
Views
280
Replies
5
Views
303
Replies
19
Views
481
Replies
1
Views
208
Replies
9
Views
189

1,207,256
Messages
6,077,319
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back