Solved

# How do I use SUMIF to sum time cells based on two criteria?

Posted on 2011-04-22
207 Views
Hello Experts,
I am trying to make a SUMIF formula work that is based on two criteria.  I've attached a sample worksheet.  Can you help?

Gary

SUMIF-Help-EE-2.xls
0
Question by:garyrobbins

LVL 30

Expert Comment

Gary you can use sumproduct to get your results for example

=SUMPRODUCT((A4:A25="Out, First")*(B4:B25))

Will sum durations where "activity" matches "Out, First"

Sid

0

LVL 6

Accepted Solution

is the duration in hours?

if so this should work:

=(SUMPRODUCT((A4:A25="Out, First")*(C4:C25>0)*(B4:B25)))*24
0

LVL 30

Expert Comment

And for your condition 2 you can use

=SUMPRODUCT((A4:A25="Out, First")*(C4:C25>0)*(C4:C25))

Sid
0

LVL 30

Expert Comment

Sid
0

LVL 6

Expert Comment

no problem, our response's aren't exactly the same, similar though, so whatever he chooses to go with that works best for him........

Brandon
0

LVL 6

Expert Comment

wouldn't yours be summing column c though?  i believe he wants column B summed............i think....
0

LVL 6

Expert Comment

the other thing, without the *24 it brings back a response of .333333..., easily solved by reformatting the cell as well, or just multiplying by 24.........either way
0

LVL 30

Expert Comment

>>>no problem, our response's aren't exactly the same,

Yeah :)

I just gave Garry two conditions like he wanted.

Sid
0

LVL 30

Expert Comment

>>the other thing, without the *24 it brings back a response of .333333..., easily solved by reformatting the cell as well, or just multiplying by 24.........either way

Yeah. I reformatted the cell :)

Sid
0

Author Closing Comment

When Experts compete, I win!  I not only get fast results, but also helpful variations.

Great responses guys -- thank you.

Gary
0

LVL 30

Expert Comment

I feel as if I have wasted my time on this thread...

Sid
0

## Featured Post

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…