Formula Assistance

r0030

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?

Michael Culley

What about like =countifs({submission type}, Onboarding@row,{Talent 1}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) + countifs({submission type}, Onboarding@row,{Talent 2}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) etc etc

Is that what you're looking for?

Michael Culley

=countifs({submission type}, Onboarding@row,{Talent 1}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) + countifs({submission type}, Onboarding@row,{Talent 2}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) + countifs({submission type}, Onboarding@row,{Talent 3}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 4}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 5}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 6}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 7}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 8}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 9}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 10}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))

r0030

I think it is pulling in the correct number for what I'm telling it to return, but maybe the syntax is off. I think it should be maybe a collect or something, but not exactly sure.

For example, using our formula for the month of June, it returns the value 3. This is technically correct in terms of the number of line items that meet the criteria. The problem is that the number should be 30, because I want it to count every cell within the Talent # 1 - Talent # 10 columns if criteria is met.