Sum of differences using SUMIF?

[Bobo]

Basic question that I've had difficulty finding on the net:

I have one column that has recurring labels ("CF" and "CG" fo
example). I have another column with the corresponding start time an
yet another column with the end time.

CF 9:00 - 17:00
CG 17:00 - 19:00
CF 19:00 - 21:00
(etc)

What I want to do is sum up the total number of hours for CF and CG.
Right now I have tried the following formula to sum up the hours for C
(which has a detected error):

SUMIF(M3:M13,"=CF",O3:O13 - N3:N13)

Column M has the label and columns N and O have start and end times
respectively.

I'm sure it's something basic...excel formulas aren't exactly intuitiv
(at least for me). While I wait for a response, I'll be looking up th
significance of the $ operator in ranges.

Thanks!

- Nat

 

[Andy B]

Hi

Try this:

=SUMPRODUCT((M3:M13="CF")*(O3:O13 - N3:N13))
Format the cell as [h]:mm

 

[Jason Morin]

Try:

=SUMPRODUCT((M3:M13="CF")*(O3:O13-N3:N13))

and custom format the formula cell as [h]:mm.

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(M3:M13="CF"),(O3:O13-N3:N13))
and format the resulting cell with the custom format
[hh]:mm

 

[Bobo]

Makes sense...I should just forget about sumif, eh? Seems like sum o
products is the best way to go for nearly all counting or conditiona
summation problems.

Thanks a ton...this website is definitely getting bookmarked.

- Nat

 

[Aladin Akyurek]

Makes sense...I should just forget about sumif, eh?
No.

Seems like sum of
products is the best way to go for nearly all counting or conditional
summation problems.

Only if you need a multiconditional counting or summing.

 

[Bobo]

Ok, how about this one...

Let's say I am still attempting to solve the original problem, but
make this modification: comments can be placed between labeled lines.

Example:

CF 2:00 4:00
-->Reading Dan's Proposal
CG 16:30 18:00
-->Experimenting with OpenGL
-->Working on visualizer prototype
CF 18:30 20:00
-->Meeting

Note that the description is in the same column as the start time.

Now, I can't perform arithmetic operations on the lines of text...o
I'll get a VALUE error. I can't use an IF because the IF format woul
be something like IF(NOT(ISBLANK(M3:M13)),O3:O13-N3:N13,0) and O3:O13
N3:N13 doesn't seem to want to work. Furthermore, SUMPRODUCT wil
attempt to calculate both pieces of the product even if one of th
factors evaluates to 0. (or at least it seems that way)

So...how can I use a conditional statement to avoid the error?

- Nat

 

[Domenic]

Hi Nate,

try,

=(SUM(IF((M1:M15="CF")*(ISNUMBER(O1:O15)),O1:O15))-SUM(IF((M1:M15="CF")*(
ISNUMBER(N1:N15)),N1:N15)))*24

entered using CTRL+SHIFT+ENTER

Hope this helps!