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)))

However, everything I've tried is returning either an error message or the number 0 - neither of what I want. Could anybody provide assistance? Thank you in advance!

MedaUser

I personally don't have much luck with the ISBLANK formula. Have you tried replacing those with the following?

=NOT(@cell="")

MedaUser

I personally don't have much luck with the ISBLANK formula. Have you tried replacing those with the following?

=NOT(@cell="")

r0030

I hadn't thought to try that, but it seems like it's working now! Thank you so much!

MedaUser

Great! Happy to help!

r0030

@MedaUser Circling back to this and making it a little more complicated ;)

Is there a way to achieve the same things above, but instead of just counting if the items meet the criteria, I need to count the number of "Talent" that are associated with each line. 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?

MedaUser

I think this makes sense. Are you getting an error right now or is it counting inaccurately?

If error, then I would suggest replacing the NOT(ISBLANK(@cell)) with what I suggested above NOT(@cell ="").

If counting inaccurately, then I'll need to understand if the number populating for you is lower or higher than you are expecting.

r0030

Currently it's not generating an error, but pulling inaccurately. I replaced NOT(ISBLANK(@cell)) with NOT(@cell="") but it is unfortunately giving the same result. Below is the formula I used:

=COUNTIFS({Submission Type}, Classification@row, {Talent 1}, NOT(@cell = ""), {Talent 2}, NOT(@cell = ""), {Talent 3}, NOT(@cell = ""), {Talent 4}, NOT(@cell = ""), {Talent 5}, NOT(@cell = ""), {Talent 6}, NOT(@cell = ""), {Talent 7}, NOT(@cell = ""), {Talent 8}, NOT(@cell = ""), {Talent 9}, NOT(@cell = ""), {Talent 10}, NOT(@cell = ""), {Date Submitted}, AND(@cell >= DATE(2022, 6, 1), @cell <= DATE(2022, 6, 31)))

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

For example, the number returned using the above formula for the month of June 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.