Single Column, Mutiple titles: Need a way to (sum) them.

[Nasty Wet Smear]

Good morning, guys and girls.

I've got a problem that might be very basic and I'm just confused or it might be totally impossible and I was right the first time.

What we have is a report that produces a single column with multiple areas identified by numbers starting with 002-xxxyyy. After that comes a series of employee numbers and a second column of digits that need to be added together. The titles repeat, so there may be 4 instances of 002-xxxyyy in the column and 3 instances of 002-yyyxxx.

At the moment our only solution is to manually Copy and Paste the identical 002-xxxyyy numbers and the employee numbers under them into their own, individual columns so that a (sumif) can be used. Employees can work in multiple locations so we can't just (sumif) by employee numbers. Ultimately what we'd really like is a formula that searches for a relevant 002- number, adds everything in the second column under that number and stops before the next 002- number... Then continues to search for the remaining matching 002- numbers and do the same all in that one column.

What's the story? Are we living in a crazy fantasy world or can it be done?

I've included a little screenshot of some made up numbers as an example of what we'd be looking at from the report when it's generated. You'll see that we can't use a filter and, because the 002- numbers are mixed we can't just copy and paste in sections. We lose whole hours of our day breaking this task down into manageable chunks before a templated sheet can do its work.

Thanks, everyone!

[Pete_UK]

Many contributors cannot view pictures on this Forum due to software incompatibilities with some browsers. It would be better if you can attach a sample Excel workbook instead.

Pete

[Nasty Wet Smear]

Ah, very well. Sorry about that.

Thanks for the heads up, Pete.

As you'll see in the attached, for privacy reasons (Juussttt in case) I've replaced all actual employee numbers with "Employee number XX", however the 002- numbers are the crucial ones. They tell us where people where and they can repeated a dozen times or more. I've cut this one short to stop it being several megs big, but they are repeated in that order over and over and, at the moment, the only way to get the relevant data out is to manually go through and cut and paste each group of data.

There goes your Monday morning, right?

Also, while the report messes with the format, it is an excel 2007 worksheet we use to paste the data into, so all formals can be compatible that 2007.

Thanks again!

[bebo021999]

Try in B2:

=SUM(IFERROR(--($B3:INDEX($B3:$B$327,MATCH(4,SEARCH("-",$A3:$A$327),0))),0))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Format as [hh]:mm

Copy to B41,...

[Nasty Wet Smear]

Thank you for taking the time to reply.

This doesn't seem to have had the desired effect. This added up the first series of numbers, which is fine, but didn't identify that there was another, matching "002" number down the sheet that contained other numbers that needed to be added. It gave me an answer of 63:45, which is true of the first series 002, but doesn't include the additional 23:15 located at A316.

[bebo021999]

So it fail at the last set?

So if the last row is 327, try to type "002-" in cell 329, then expand the range to row 329

B316:

=SUM(IFERROR(--($B317:INDEX($B317:$B$329,MATCH(4,SEARCH("-",$A317:$A$329),0))),0))

[Nasty Wet Smear]

I'm very sorry, but I'm not sure I'm following your instructions, as it doesn't seem to help much. Would it be too much to ask if maybe you could add your forumla to the attachment I included in my second post and then for you to attach it here? That way I can be sure I'm doing what you ask and I'll see if the results are what I'm looking for?

I know it's a head ache, but I'd be grateful.

[bebo021999]

Here you go..............

[Nasty Wet Smear]

Ah, okay. No, I'm sorry. Not quite what I'm looking for.

If you look at the 002- numbers you'll see that each is slightly different. Your formula seems to add them all together, which isn't a problem. What I need the forumal to do is add all the identical codes without including the other codes. The issue being that the forumla needs to identify when a code starts and when a code ends and when a second, identical code starts and add up only the amounts between them.

So, for example: On that test page all the 002-2101 numbers add up to 87. That's the 002-2101 numbers that go from B4 - B37 and from B318 - B327.

I spose, using that formula, I could add up those numbers with a (Sumif) forumla after that. Is that what you were thinking?

Thanks.

[bebo021999]

OK it is clear now.
Try attachment.
If it does not work, try to put your expected results then upload again.

[Pete_UK]

I've taken Quang's file and just produced a summary table in columns E and F, so all the totals are bunched together. I've used this array* formula in E2:

=IFERROR(INDEX($C$3:$C$327,MATCH(0,COUNTIF($E$1:E1,$C$3:$C$327),0)),"")

which produces a unique distinct list of the job numbers when copied down, and used this array* formula in F2:

=SUMPRODUCT(($C$3:$C$327=E2)*IFERROR(B$3:$B$327+0,0))

which totals the corresponding numbers from column B and puts them in Excel time format. I also deleted column D from Quang's sheet.

I've copied the formulae down 10 rows in this example, but you may need to copy them further in your real file. You will also need to change the reference to the bottom line (shown red above).

* NOTE: An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual |Enter|. If you do this correctly then Excel will insert curly braces { } around the formula when viewed in the formula bar, but you must not type these yourself. If you need to amend the formula you will have to use CSE again to commit it. The formula can be copied down in the usual way(s).

Hope this helps.

Pete

[Nasty Wet Smear]

Unbelievable! You guys are freaken amazing. Thank you both for your assistance. Next time you’re in Adelaide head over and have a drink on me!

[bebo021999]

Unbelievable! You guys are freaken amazing. Thank you both for your assistance. Next time you’re in Adelaide head over and have a drink on me!

Sure! You are welcome.

[Pete_UK]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Nasty Wet Smear]

Consider both things done!