Sumif ?

Brandt · Jun 23, 2009

Hi !
I will try to explain cleary as possible using my poor English skills...Please, excuse my grammar mistakes. I have a table where in A column I named equipments, in B column I put the type of maintenance ((C)orretive or (P)reventive, in C column I put start time, in D column I put end time and, finally, in E column I put the time expended:

january february . . .

A B C D E F G H I . . .

ZX123 C 12:00 12:30 00:30 P 13:30 14:30 01:00
BRR45 P 14:30 15:20 00:50 C 13:00 15:00 02:00

So, how can I:

- sum all corretives maintenances until now ? Using exemple above, until now (june) were performed one corrective maintenance and one preventive maintenance in equipment ZX123, and the equipment remain stopped one hour and 30 minutes.

Thanks,

Brandt

Andy B · Jun 23, 2009

Where do you show the months, i.e. are these on separate sheet tabs, if so do we need to identify which year they belong to?

Also, is here a limit to the number of maintenances that can be performed on each row?

shemayisroel · Jun 23, 2009

Brandt, could you post some sample data and expected result(s) using excel jeanie

http://www.excel-jeanie-html.de/index.php?f=1

Peter_SSs · Jun 24, 2009

Brandt

Is this the sort of thing you mean?

Formula in L4 is copied across to M4 and down. Formula in N4 is copied down.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

2

january

.

february

Total

3

Type

Start

Stop

Time

Type

Start

Stop

Time

C

P

Time

4

ZX123

C

12:00

12:30

0:30

P

13:30

14:30

1:00

1

1:30

5

BRR45

P

14:30

15:20

0:50

C

13:00

15:00

2:00

1

2:50

6

ABC

P

14:30

15:20

0:50

P

13:30

14:30

1:00

0

2

1:50

Sum

Brandt · Jun 24, 2009

Peter_SSs said:
Brandt

Is this the sort of thing you mean?

Formula in L4 is copied across to M4 and down. Formula in N4 is copied down.

Excel Workbook
A B C D E F G H I J K L M N
2 january . . . february Total
3 Type Start Stop Time Type Start Stop Time C P Time
4 ZX123 C 12:00 12:30 0:30 P 13:30 14:30 1:00 1 1 1:30
5 BRR45 P 14:30 15:20 0:50 C 13:00 15:00 2:00 1 1 2:50
6 ABC P 14:30 15:20 0:50 P 13:30 14:30 1:00 0 2 1:50
Sum

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

2

Type

Start

End

Stopped Time

Total of "C"

Total of "P"

Total time stopped

3

JAN

FEB

. . . .

DEC

4

CA250

C

12:20

14:20

02:00

P

08:30

12:30

04:00

. . . .

C

14:30

15:30

01:00

5

1

12:20

5

C

15:00

17:00

02:00

C

12:00

13:30

01:30

. . . .

6

C

08:30

09:20

00:50

. . . .

7

FX123

C

13:00

14:00

01:00

. . . .

P

17:00

21:00

04:00

2

1

06:00

8

C

14:20

15:20

01:00

. . . .

First of all, I would like to thank to shemayisroel, Andy B and Peter. Using Excel Jeanie (thanks shemayisroel) now I can to explain better, but I think Peter's formulas work fine. Sheet1

shemayisroel · Jun 24, 2009

Brandt said:
First of all, I would like to thank to shemayisroel, Andy B and Peter.
Using Excel Jeanie (thanks shemayisroel) now I can to explain better, but I think Peter's formulas work fine.

No problems, good that you got what your looking for.

Brandt · Jun 24, 2009

shemayisroel said:
No problems, good that you got what your looking for.

Oh, just another question: how can I separate time spent in C and time spent in P ?

shemayisroel · Jun 24, 2009

Brandt said:
Oh, just another question: how can I separate time spent in C and time spent in P ?

Not sure I follow you, could you elaborate?

Peter_SSs · Jun 25, 2009

Brandt said:
Oh, just another question: how can I separate time spent in C and time spent in P ?

Formula in N4 copied across and down.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

2

january

february

Total

Time

3

Type

Start

Stop

Time

Type

Start

Stop

Time

C

P

C

P

4

ZX123

C

12:00

12:30

0:30

P

13:30

14:30

1:00

1

0:30

1:00

5

BRR45

P

14:30

15:20

0:50

C

13:00

15:00

2:00

1

2:00

0:50

6

ABC

P

14:30

15:20

0:50

P

13:30

14:30

1:00

0

2

0:00

1:50

Sum

Brandt · Jun 27, 2009

Hi !

Just to thanks everybody.

Congratulations too because in this message board respect and friendship are present. Thank you so much.

Brandt

Sumif ?

Brandt

New Member

Excel Facts

Andy B

Active Member

shemayisroel

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Brandt

New Member

shemayisroel

Well-known Member

Brandt

New Member

shemayisroel

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Brandt

New Member

Similar threads

Forum statistics

Share this page

Sumif ?

New Member

Excel Facts

Active Member

Well-known Member

MrExcel MVP, Moderator

New Member

Well-known Member

New Member

Well-known Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock