Sum a cell based on criteria of 2 lots of different cells being the same

[Allen_Mead]

Hi, the title probably doesn't make sense so hopefully my explanation will.

I need to total the number of days spent on a task in lets say cell B8 but this is controlled by the task competency associated with this which is entered in cell C8 and, there is 3 levels of competency which are entered in D8

This is what I have if, I don't want the level to also control the output which works nicely.

=IF($C8=$J$7,SUM($B8),0)

Perfect.

However, when I want to also control the answer by the level which in this case is comparing cells D8 and J9 I come unstuck. I did have

=IF($C9=$J$7&$D9=$J8,SUM($B9),0)

but I just get a zero.

Would really appreciate some help. Thanks.

 

[jasonb75]

Perhaps

=IF(AND($C9=$J$7,$D9=$J8),SUM($B9),0)

If it still comes up with 0 when you expect a different result then try checking the cell references in the formula, I notice that you said D8 and J9 in your post while your formula had D9 and J8.

 

[Allen_Mead]

Perhaps

=IF(AND($C9=$J$7,$D9=$J8),SUM($B9),0)

If it still comes up with 0 when you expect a different result then try checking the cell references in the formula, I notice that you said D8 and J9 in your post while your formula had D9 and J8.

That's perfect, thank you.