COUNTIFS | 2 CRITERIA FROM SAME COLUMN | REFERENCE SHEET

MikeChap
MikeChap ✭✭
edited 12/09/19 in Formulas and Functions

Kind Sir/Madam, 



Greatly appreciate any help on the following:



I created an extra sheet with formula's so I can have them displayed in a dashboard without having to add all kind of formulas and outcome of these formulas in my source sheet. My goal is to count the number of "approved" and "completed" quotations (same column) in a reference sheet.



Short crash course in Dutch:

AANVAARD means approved

AFGEROND means completed 



With one criteria it works fine:

=COUNTIF({Fase}; "AFGEROND")



But when I add the second criteria, please note it has to be either the first "AANVAARD" or "AFGEROND" 



I've tried to use the @cell way as was advised by Brian W in this article :



=COUNTIFS({Fase}; @cell = "AANVAARD", {Fase}; @cell = "AFGEROND"))

but I get an "UNPARSEABLE" error



I also tried this:

=COUNTIFS({Fase}; "AANVAARD"; {Fase}; "AFGEROND")

but ofcourse it comes up with zero, so I added OR to the formula:

=COUNTIFS({Fase}; "AANVAARD"; OR {Fase}; "AFGEROND")



I called Smartsheet for help. Very friendly staff but they needed some time to figure out the formula and I just cannot stand it them I'm not able to figure this one out.



Thanks for the help, much appreciated. 



Kind regards, 

Mike

Tags:

Comments

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try this: 

    =COUNTIFS({Fase}, OR(@cell = "AFGEROND", @cell = "AANVAARD"))

    or

    =COUNTIFS({Fase}; OR(@cell = "AFGEROND"; @cell = "AANVAARD"))

  • Ah man, bullseye !! thank you Nic. Would you be willing to help me out on the next one too?

    Now I know the number of definitive quotations/bookings that have the status "approved" or "completed". But here's where it gets tricky (for me at least) now I have to display the amount (in Dutch "offertebedrag") of these "approved: or "completed" bookings in euros of all future bookings.



    So far I came up with a formula for only 1 criteria "AANVAARD" but no luck up untill now:

     

    =SUMIFS({Offertebedrag}; {Fase}; "AANVAARD"; {Datum boeking} >= TODAY(0); {Offertebedrag})

     

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    This should sum any that meet these 2 criteria.

    =SUMIFS({Offertebedrag}; {Fase}; OR(@cell = "AANVAARD"; @cell = "AFGEROND"))

    I see you have a Date criteria in there though. Can you tell me what else you are evaluating and I can try and add that in as well. 

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Same formula as above but now also looking at those that are dated in the future (greater than today):

    =SUMIFS({Offertebedrag}; {Fase}; OR(@cell = "AANVAARD"; @cell = "AFGEROND"); {Datum boeking}; >TODAY())

  • MikeChap
    MikeChap ✭✭
    edited 04/29/19

    Managed to figure it out, thanks for help. It's 01:30 AM in my country and because of your help I can now get some sleep :-) I'm a newbee to this community but it's always nice to see people help each other out. Thanks again. Take care. Bye. 

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Perfect glad to help. This is the best place to learn. Lots of good folks here to assist.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!