Sort and sum

[zigtag3d]

My previous post may have been confusing OR it may have seemed that it was more lengthy than I needed to put it so let's see if I can simplify my problem...

Column A is TIME format and B is time but in seconds.

I can get it sorted but not sure how to add the values in B1 that fall in the same TIME in A1.

In other words - I have 3 entries in A1 (9:30) that are the same and the corresponding values need to be added together and that total placed on another sheet.

Keep in mind that the number of duplicate A1 times may vary depending on number of people working.

Thoughts?

[swatsp0p]

Let's use an IF statement and a SUMIF function. For your example, with data beginning in A2 & B2, in C2 place this formula and copy down the range:

=IF(A3<>A2,SUMIF($A$2:$A$10,A2,$B$2:$B$10),"--")

Of course adjust the ranges listed to meet the needs of your actual data.

This looks one cell below the target cell to see if the time changes. If it does, it take the value in the target cell (A2 in the formula above) and SUMS all of the values in B that have a matching value in A.

For those rows that the time doesn't change, "--" is returned in col. C

HTH

Bruce

[zigtag3d]

Thanks .... that works and I can get the totals to the right place. BUT I realized that there is a problem with my sorting that I am not sure how to deal with.

The data is brought in and it shows the way I need it and the times are showing as time format. Here is the problem:

See the problem? If I sort then the times are not in the correct order to transfer to the corresponding location on another sheet. The time period I am working with is from 8am to 11pm.

How would I ensure that the times in the first column are actually correct? How would I make sure that anything listed later than 1230pm was actually a PM time? I tried messing around with the format but the data as it comes in is already pulling up as a time format so it doesn't mak a difference.

Thoughts on that one? Maybe I should repost asking how to sort by TIME?

Jack

[swatsp0p]

Have you tried to format your times as "13:30" format? If that doesn't work, are your sure your times are truly imported as TIME (that Excel interprets as a time code)? What does 1:30 PM look like in your data? Change the format of the cell to General and see if it changes to a decimal number like 0.5625 (1:30 PM) or 0.0625 (which is 1:30 AM).

If it doesn't look like one of the prior examples, it is probably being treated as a Text entry. Use Data>Text to Columns on your range and select General on the last tab, then choose the 13:30 time format for your range.

HTH

[zigtag3d]

The time visually looks like "1:30". The formula bar shows it as "1:30:00 AM". When you check the format of the cell it is TIME (13:30)... If I change the format to general I do get the decimal of 0.0625 so it is being interpreted as TIME and it really is being read as 1:30AM.

I got to thinking about the situation and looking at the data. The 3rd column is also a "time" but it is formated as General so the times in that column would be "1:30PM" as a general format so I guess it would be treated basicly like "text"?.

Is there a way I could use them as a reference for sorting and get everything to fall where it should? My time intervals are 30min each --- 8:00-8:30, 8:30-9:00.....12:30-1:00, 1:00-1:30 and so on. The sort needs to be correct running 8:00AM thru 11:00PM.


Jack

Have you tried to format your times as "13:30" format? If that doesn't work, are your sure your times are truly imported as TIME (that Excel interprets as a time code)? What does 1:30 PM look like in your data? Change the format of the cell to General and see if it changes to a decimal number like 0.5625 (1:30 PM) or 0.0625 (which is 1:30 AM).

If it doesn't look like one of the prior examples, it is probably being treated as a Text entry. Use Data>Text to Columns on your range and select General on the last tab, then choose the 13:30 time format for your range.

HTH

[swatsp0p]

Your information now begs the question: How does it handle 8:00 PM - 11:00 PM? Does that also show as AM?

You may need to use column C as your 'sorting' column and refer to those times for your calculations. Certainly, an option is to add 12 hours to those times that should be PM but are AM in a Helper column, however if duplicate times AM/PM are indistingishable, this is not practical.

Good Luck