Excel SumProduct extract Delimited Text and Numbers and sum from array of text

[jc983]

Hi. I have names and numbers on a budget. Starting with A1 through A7 the text looks like this. There may be blanks as well.

Data
Bob-15
Jon-40
Bob-10
Bob-10

Jeff-35
Bob-10


I want a formula to summarize the hours by person on the array. Results to show this:

User Hours
Bob 45
Jon 40
Jeff 35

Is there a way to do this, maybe with SumProduct? I'm using Excel 2013. THanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

Why don't you split your data into two columns using Text To Columns and - as the delimiter?

 

[Comfy]

Something like this:

Excel 2010
	A	B	C	D
1	Data
2	Bob-15		Bob	45
3	Jon-40		Jon	40
4	Bob-10		Jeff	35
5	Bob-10
6
7	Jeff-35
8	Bob-10
Sheet1

Cell Formulas
Range	Formula
D2	{=SUM(IFERROR(--SUBSTITUTE($A$2:$A$8,C2&"-",""),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note - this will only work in the very specific circumstance that the name is followed by a hyphen.

 

[Special-K99]

in B1
=IF(A1="",0,RIGHT(A1,LEN(A1)-FIND("-",A1)))

and copy down the column

in C1 going down
Bob
Jon
Jeff

in D1
=SUMPRODUCT((LEFT(A$1:A$7,3)=C1)*(B$1:B$7))
and copy down to D3

UPDATE: Go with Comfy's reply, it's much cleaner than mine

 

[jc983]

Really appreciate the help! This is the first one I tried and it worked perfectly. Thanks everyone!

Something like this:

Excel 2010

	A	B	C	D
1	Data
2	Bob-15		Bob	45
3	Jon-40		Jon	40
4	Bob-10		Jeff	35
5	Bob-10
6
7	Jeff-35
8	Bob-10

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=SUM(IFERROR(--SUBSTITUTE($A$2:$A$8,C2&"-",""),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Note - this will only work in the very specific circumstance that the name is followed by a hyphen.