How can I count items only when 3 specific criteria are met?

PKane
PKane ✭✭✭✭
edited 12/23/22 in Formulas and Functions

Hello All,

Novice user here -

I am building a data sheet for metrics and am hoping someone could please help me figure out the best way to create a COUNTIF/IFS formula that will only count a status 'type' (Complete, Scheduled, etc.) if the Core OpCo is 'FLA' and the year is 2022 and the LFCM OT box is 'checked/True'?

I'm not sure where to begin, or if using COUNTIF/IFS is the correct way to go about it?


Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    COUNTIFS would be your best place to start. It would look Something like this:


    =COUNTIFS([DC UPS Status]:[DC UPS Status], "Complete", [Core OpCo]:[Core OpCo], "FLA", [DC UPS Year]:[DC UPS Year], "2022", [LFCM OT DC UPS Scope?]:[LFCM OT DC UPS Scope?], 1)


    See if that returns what you are looking for.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • PKane
    PKane ✭✭✭✭

    Hello Kleerfyre,

    I REALLY appreciate your help!

    I tried using the formula, but I didn't get the results I needed unfortunately. It returns #unparseable. I need to have it count these status types from a column in another sheet and pull it into my metrics sheet - that is one part that I neglected to mention. Is it possible to use a COUNTIFS in conjunction with a VLOOKUP?

    Or maybe there is a better way to go about it?

  • Hi @PKane

    You can turn a COUNTIFS formula from referencing columns in-sheet to use cross-sheet references! For example:

    =COUNTIFS({DC UPS Status column}, "Complete", {Core OpCo column}, "FLA", {DC UPS Year column}, "2022", {LFCM OT DC UPS Scope column}, 1)

    Here are some links that may help you:

    COUNTIFS Function / Create cross sheet references to work with data in another sheet


    However before changing up the references we should sort out why you're getting an error with Jonathan's formula. Can you post a screen capture of what you tried in the cell? We'll need to double check that all the column names are spelled properly and that the formula syntax is correct.

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!