BIG Question with text and number values

Posted by Nick on December 03, 2001 3:12 PM

I have a number of excel worksheet that have 7 columns. The first 5 have names in them the 6th has a time value stored in time format and the 7th has a number plus or minus. So the third row would look like this (columns are seperated by commas). Name1, Name2, Name3, Name4, Name5, 5:02, -1. I have about 20 rows of this data and I would like to take each of the combinations and sum the time and number columns. Is their anyway to have Excel look a row and make sure the combination of names is the same and then have it add the sixth column? I know this was probably poorly explained. I can send the file if anyone wants to give this one a shot. It would be a huge help.
Thanks, Nick

Posted by Adam S. on December 03, 2001 3:38 PM

It looks like you're looking for a complex Sumif

If you're looking for a quick and dirty method. Take the following:

1. Insert an 8th column with a heading with something like "criteria".

2. Assuming from your example your first row of names are in cells A2:E2, type the following formula in your new column:

=A2&" * "&B2&" * "&C2&" * "&D2&" * "&E2

The result should be:
"Name1 * Name2 * Name3 * Name4 * Name5"

Copy this formula down: this will be your criteria column for your sumifs/subtotals/or pivot table.

Hope that helps!
Adam

Posted by Nick on December 03, 2001 7:43 PM

Thanks - Still struggling

Thanks for the help. I am still struggling with where to go next. If you can give me a little more guidance. I can send a sample file if you are willing to give me a hand. My email is n_kummer@hotmail.com