I'm struggling to pull a particular formula from my data source sheet into my metrics sheet.
My criteria is to Count If:
- Submission Request is "Manager Change (Talent Only)
- Talent columns #1-10 are not blank
- Submitted Date is between August 1 and August 31
I've tried several things. The last effort being the below formula:
=COUNTIFS({Submission Type}, Classification@row, {Talent 1}, NOT(ISBLANK(@cell)), {Talent 2}, NOT(ISBLANK(@cell)), {Talent 3}, NOT(ISBLANK(@cell)), {Talent 4}, NOT(ISBLANK(@cell)), {Talent 5}, NOT(ISBLANK(@cell)), {Talent 6}, NOT(ISBLANK(@cell)), {Date Submitted}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 11, 31)))
The problem is that I'm trying to define the criteria above, but what I really need is for the formula to count all cells in my 10 Talent columns if all criteria is met.
Below is an example of how my sheet is set up for the Talent Fields:
When I enter this formula -
=COUNTIFS({Submission Type}, Onboarding@row, {Talent 1}, NOT(ISBLANK(@cell)), {Talent 2}, NOT(ISBLANK(@cell)), {Talent 3}, NOT(ISBLANK(@cell)), {Talent 4}, NOT(ISBLANK(@cell)), {Talent 5}, NOT(ISBLANK(@cell)), {Talent 6}, NOT(ISBLANK(@cell)), {Talent 7}, NOT(ISBLANK(@cell)), {Talent 8}, NOT(ISBLANK(@cell)), {Talent 9}, NOT(ISBLANK(@cell)), {Talent 10}, NOT(ISBLANK(@cell)), {Date Submitted}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))
-it populates 3 line items, which is correct. However, I want it to count all cells within Talent #1-#10 if the criteria is met.
Does that make sense?
