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

##### New Member
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.
Excel Formula:
=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
Excel Formula:
=IF(\$C9=\$J\$7&\$D9=\$J8,SUM(\$B9),0)
but I just get a zero.

Would really appreciate some help. Thanks.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### jasonb75

##### Well-known Member
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.

##### New Member
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.

Replies
4
Views
390
Replies
6
Views
84
Replies
2
Views
86
Replies
4
Views
178
Replies
4
Views
159

1,130,061
Messages
5,639,834
Members
417,117
Latest member
Ravi Pandey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back