COUNTIFS | 2 CRITERIA FROM SAME COLUMN | REFERENCE SHEET

MikeChap

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

Nic Larsen

Try this: 

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

or

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

MikeChap

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

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

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

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

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