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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
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,129,921
Messages
5,639,011
Members
417,062
Latest member
CM214

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