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

Allen_Mead

New Member
Joined
May 31, 2019
Messages
8
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
Joined
Dec 30, 2008
Messages
12,655
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 31, 2019
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top