Countif with multiple criteria

Krystab352
Krystab352 ✭✭
edited 04/17/23 in Formulas and Functions

I am trying to create a countif formula with multiple criteria with one being the approval status and the other being scheduled (which is a date and I want to count it as "1"). 

I'm trying =countifs(range1, "Approved") +countif(range 2, ???) 

The question marks if where I am having issues. The column is formatting as a date not text/number so that may be the issue. I tried =countifs(range1, "Approved") +countif(range 2, NOT(ISBLANK(@cell)) and it returned a wrong number.

Any suggestions? I'm getting really frustrated.

Tags:

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try this format. Just update my formula for your range names. 

    =COUNTIFS(date:date, NOT(ISBLANK(@cell)), [range1]:[range1], ="Approved")

  • Metric Momma
    Metric Momma ✭✭
    edited 04/17/23

    What am I doing wrong? I'm trying to count how many projects each team member is working on but I need the status of their project assignments to be In Process. I'm using this for a metrics table so I'm referencing a different report for source data.

    This one is IMPARSEABLE

    =IF({Product Bible Project Status}, "In Process"), COUNTIF([Primary Column]@row))

    This one is INCORRECT ARGUMENT

    =COUNTIF([Primary Column]@row, {Product Bible Project Status}, "In Process")

    This one returns 0 and that's not correct

    =COUNTIF([Primary Column]@row, {Product Bible Project Status} = "In Process")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Metric Momma What are you wanting to compare to the [Primary Column]@row? Proper syntax is going to be

    =COUNTIFS({Range 1}, Criteria 1, {Range 2}, Criteria 2)

  • TinaM
    TinaM ✭✭

    I am working on a countif statement across multiple workspaces using: =COUNTIFS([{Communication Status}, "Complete", {Milestone}, "30 Days post Merger Date"], [{Finance Status}, "Complete', {Finance Milestone}, "30 Days post Merger Date"]) but I keep getting #UNPARSEABLE. Am I using the incorrect formula? I have tried SUMIFS as well with the same result.


    Thanks for the look and hopefully assistance

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try removing all of your [square brackets]. They are not needed in your formula.

  • Hi all so now i am the one stuck after looking all over the place.


    i have 2 COUNTIF that work fine on their own, but i want to join them together. making was there an issue this year.

    =COUNTIF([Was there an issue during the event]:[Was there an issue during the event], 1) + ""

    =COUNTIF([Event date UTC]:[Event date UTC], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))


    if you allso know how to do a this quater instead of year that would be amazing

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!