Sum of differences using SUMIF?

  • Thread starter Thread starter Bobo
  • Start date Start date
B

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
 
Try:

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

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

HTH
Jason
Atlanta, GA
 
Hi
try
=SUMPRODUCT(--(M3:M13="CF"),(O3:O13-N3:N13))
and format the resulting cell with the custom format
[hh]:mm
 
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
 
Bobo > said:
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.
Click to expand...

Only if you need a multiconditional counting or summing.
 
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
 
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!
 
Back
Top