Sum and Count problem

[sharky12345]

I need the solution for this problem folks and I know one or more of you will come up trumps!

I have a sheet with 3 columns, the 1st has the date in it, formatted to '1st May 2011' for example. The second column has a figure between 1 and 8, known as 'actions' - the third column has a time as 11:15, (for example), and I have set a cell to calculate the total time.

What I need is for a seperate cell to calculate how much time was spent on action 1 in January, so the cell will only sum the time for instances of 'January' and 1 - does this make sense??

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--(MONTH(A1:A100)=1),--(B1:B100=1),C1:C100)

The 1's can be replaced with references to cells that contain those values.

 

[VoG]

Perhaps

=SUMPRODUCT(--(MONTH(A1:A100)=1),--(B1:B100=1),C1:C100)

Format the result cell as Custom [h]:mm

 

[sharky12345]

Gents - many thanks, I knew it wouldn't take long!

Have a great weekend!

 

[sharky12345]

Gents - just a thought, could the formula be amended to include a third column, which has letters ranging from A to E?

 

[mealypudding]

I am having a similar problem. I have a rota with different shifts and im trying to calculate if they fall under early(7-11)afternoon(12-14) or late(17+)

Example values
0600-1400
1200-2100
1500-2300
0700-1200/1700-2300

=SUMPRODUCT(--(LEFT(B10:B20,4)<"1100"),--(B10:B20<>""))
im using this formulae to calculate if it's an early shift
=SUMPRODUCT(--(RIGHT(B10:B20,4)>"1700"),--(B10:B20<>""))
this one to calculate the late shift

Im finding it hard to write the formulae to calculate the afternoon shift
I bacially want to know if their shift covers the time period 1200-1400

Any ideas?
Thanks in advance for any help

 

[VoG]

Gents - just a thought, could the formula be amended to include a third column, which has letters ranging from A to E?

Try like this

=SUMPRODUCT(--(MONTH(A1:A100)=1),--(B1:B100=1),--(D1:D100="A"),C1:C100)

 

[sharky12345]

That works - thanks again!