Using CountIf to find total number based on 2 different cells?

JERRADV

New Member
Joined
Sep 17, 2024
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
I need help coming up with a formula for the following:

  • IF the context of a cell in the "PRIMARY TASK" column (column J) says "Appraisals" THEN count the cells in the corresponding row in the "TENURE IN WEEKS" column (column L)

and then vice-versa:

  • IF the context of a cell in the "PRIMARY TASK" column (column J) says "Order Outs" THEN count the cells in the corresponding row in the "TENURE IN WEEKS" column (column L)

For context, I am trying to find 2 different data points:
  • The total number of people we have working Appraisal tasks and how many are at what week tenure
  • The total number of people we have working Order Out tasks and how many are at what week tenure
This will help me figure out, for example, that I have 8 people working the Appraisal Task and 4 are at 2 weeks tenure, while 1 is at 5 weeks tenure and 3 at 11 weeks tenure. Therefore, now knowing how many I have working Appraisals and how many are at what tenure, I can calculate their expected daily productivity based on the daily goals they are suppose to meet at that specific tenure. Which I can then calculate the total number of expected tasks that we should be able to work in a day.

Can anyone assist? Thank you!
 

Attachments

  • Excel Example.PNG
    Excel Example.PNG
    14.5 KB · Views: 7

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Is this what you're looking for?
Book1
ABCDEFGHI
1Tenure (in weeks)CampusPrimary Task
2-GPO-Weeks:5255075
3-GPO-Appraisals0002
4#ValueGPOAppraisalsOrder Outs2557
554GPOOrder Outs
6#ValueGPOAppraisals
7#ValueGPOAppraisals
863GPOOrder Outs
969GPOAppraisals
1056GPOAppraisals
11#ValueGPOOrder Outs
1220GPOOrder Outs
1313GPOOrder Outs
1411GPOOrder Outs
15#ValueGPOOrder Outs
16#ValueGPOOrder Outs
17#ValueGPOOrder Outs
18#ValueGPOOrder Outs
192GPOOrder Outs
202GPOOrder Outs
Sheet1
Cell Formulas
RangeFormula
F3:I4F3=COUNTIFS($C$2:$C$20,$E3,$A$2:$A$20,"<="&F$2)
 
Upvote 0

Forum statistics

Threads
1,224,596
Messages
6,179,807
Members
452,944
Latest member
2558216095

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
Back
Top