i'ld like to do a conditional sum with text

March 31, 2011 at 03:44:21
Specs: Windows 7
I've this data
A B C
1 Date Device Duration
2 Mon, 3/28/2011 office connection 1 hour, 2 minutes, 43 seconds
3 Mon, 3/28/2011 office connection 27 seconds
4 Tue, 3/29/2011 house connection 2 minutes, 1 second
5 Mon, 3/28/2011 shop connection 4 minutes, 0 seconds
. . . .
.
.
100

The question is: i want another sheet to sum the duration of each connection for each day. Sample output calculated in the other sheet would be:
A B C
1 Date Device Total duration
2 Mon, 3/28/2011 office connection 1 hour, 2 minutes, 70 seconds
3 Mon, 3/28/2011 shop connection 4 minutes, 0 seconds
4 Tue, 3/29/2011 house connection 2 minutes, 1 second

and so on.
I've tried to use SUMIFS(C2:C100, B2:B100, =office connection, A2:A100, =Mon, 3/28/2011) but the value returned is zero. Is there any way to get this done


See More: ild like to do a conditional sum with text

Report •


#1
March 31, 2011 at 13:52:22
If that's what your data looks like, then I doubt you will ever get it to work.

Post an example of what is actually in your spreadsheet.

Read this how-to to learn how to post your data in this forum:

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

#2
April 1, 2011 at 00:55:03
  


Report •

#3
April 1, 2011 at 01:01:55
  


Report •

Related Solutions

#4
April 1, 2011 at 01:43:40
         A                 B             C
1    Date              Device        Duration
2    Mon, 3/28/2011    office     1 hour, 2 minutes, 43 seconds 
3    Mon, 3/28/2011    office     27 seconds
4    Tue, 3/29/2011    house      2 minutes, 1 second
5    Mon, 3/28/2011    shop       4 minutes, 0 seconds
.
.
.
100

Now i would like to sum the durations for each device in another sheet so that my output would look like this
         A              B            C
1     Date             Device        Total duration
2     Mon, 3/28/2011     office     1 hour, 2 minutes, 70 seconds
3     Mon, 3/28/2011     shop       4 minutes, 0 seconds
4     Tue, 3/29/2011     house      2 minutes, 1 second



I have tried using the SUMIFS(range, criteria range, criteria, criteria range, criteria) but the value returned is 0. Is there any way to get this done? I hope my presentation is better now.

Report •

#5
April 1, 2011 at 07:00:13
Your Time column ( column C ) is going to give you all kinds of headaches.

The usual Format for a time cell is
HH:MM:SS = Hours:MINUTES:SECONDS = 01:02:43

How are you entering the data into column C?
Is it by hand or do you have a formula?
What does the formula look like?
Are you Custom Formatting your TIME cells?

Also, are you using a Custom Format for you DATE column?

MIKE

http://www.skeptic.com/


Report •

#6
April 1, 2011 at 07:06:45
I get the data from a monitoring software in that format and then need to do a summarry which involves calculating the total duration so yes the data is gotten somewhat manually since i just copy and paste it

Report •

#7
April 1, 2011 at 07:22:20
If your getting your data from another source, and just doing a copy/paste, then all your data is in TEXT format.

You will need to convert your DATE column to real Dates,
and your TIME column to real Times.

First,

To convert your DATE column try this:

Open a new column NEXT to column A, so you now have a new empty column B
In cell B2 enter the formula:

=DATEVALUE(RIGHT(A2,9))

and drag down as many rows as needed.

You should see the same date in Column B as there is in column A

Now, in Column B, select your whole range of new Dates and
On the Task Bar
Select Copy
Select Paste Special
Select Values
Click OK

The Dates in Column B are now real dates and not just the result of a formula.

See how that works.

MIKE

http://www.skeptic.com/


Report •

#8
April 1, 2011 at 07:33:29
ok i get the dates in the format Mon, 3/28/2011 becomes 3/28/2011.
What next?

Thanks for the first step.


Report •

#9
April 1, 2011 at 07:38:51
OK, but first here is a modifed formula for the Date's,

=DATEVALUE(RIGHT(A2,10))

The old one will give you an error message if the date is 10 characters long,
so a date of 3/28/2011 which is nine charters long will be OK,
but a date of 12/12/2011 won't work.

When I have a fix for the Time values will post it.

MIKE

http://www.skeptic.com/


Report •

#10
April 1, 2011 at 10:54:51
I wasn't able to come up with one formula for all
the possible conditions in your Time column.

So, here are two formulas that should do the job.

If a cell has Hours, Minutes and Seconds
or a cell has Minutes and Seconds
use this formula:

=IF(COUNTIFS(D2,"*hour*"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"seconds",""),"minutes,",":"),"hour,",":"),"0:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"seconds",""),"minutes,",":"),"hour,",":"))

(It is a very long formula, copy and paste it)

If a cell has only Seconds
use this formula:

="00:00:"&LEFT(D3,FIND(" ",D3,1))


Use two blank columns, lets say column E & F

In column E enter the two above formulas to get
just the Hours, Minutes and Seconds into a format
that Excel will understand.

In column F enter the formula =TIMEVALUE(),
it works the same as the =DATEVALUE() formula.

Then once you have converted all your times do a Copy / Paste Special / Values.

See how that works.

MIKE

http://www.skeptic.com/


Report •

#11
April 1, 2011 at 11:18:18
If, after you do the =TIMEVALUE(D2) section and you see:

0.043553241
0.0003125
0.043553241
0.01875
0.001400463

That is how Excel actually stores the Time, just format the column as HH:MM:SS

MIKE

http://www.skeptic.com/


Report •

#12
April 5, 2011 at 06:32:07
Thanks for the reply. It works quite well but for the cases where my cell contains say data that isn't plural say "1 hour" , "1 minute" or '1 second' or when the data is say "1 hour, 23 minutes" with no seconds involved, in such cases i need to add ", 0 seconds" to get the correct format. is there any way around this?

Apart from that I'm getting quite good results. Thanks alot.


Also, is it possible to do some OR kind of statement so it checks for hour or hours, minute or minutes and second or seconds.


Report •

#13
April 5, 2011 at 14:37:51
The possible combination of different formats makes it extremely difficult to use this data effectively and trying to write one formula or even two or three to fit all possibilities is daunting.

With just the three words Hour, Minute, and Second you can have about 7 different combo's:

Hour Minute Second
Hour Minute
Hour Second
Minute Second
Second
Hour
Minute

add in plurals to one or more words........and you can see how it just grows.

You should try to talk to who ever is in charge of the monitoring software and see if they can modify the output to be more Excel friendly.

Or you may be better of pre-processing the date before importing it into Excel.

There is probably a VBA solution to your problem, but unfortunately my VBA skills are nill.

Perhaps someone will jump in with a VBA solution.

MIKE

http://www.skeptic.com/


Report •

#14
April 6, 2011 at 00:46:47
Thanks, I see it is more complicated than I thought.
I'll make do with what I have so far and hope there's someone with a VBA solution (whatever that is) like you said.

Report •

#15
April 6, 2011 at 07:06:13
Visual Basic for Applications (VBA),
it's a programing language used inside Excel,
you can do a lot of wonderful and magical things with it.

When you create a Macro your using VBA.

MIKE

http://www.skeptic.com/


Report •

Ask Question