Countifs With Multiple Criteria
I am trying to count the totals if a certain criterias are met. Per example below I am trying to count if client is A and B where "Initiative" is NOT "N/A" and Progress for both "Waiting for Pricing".
I tried the formula below and not correct syntax and logic. Any help would be much appreciated.
=IF(COUNTIFS({Client}, ="A, {Initiative}, <> "N/A", {Progress}, = "Waiting for Pricing"), COUNTIFS({Client}, ="B", {Initiative}, <> "N/A", {Progress}, ="Waiting for Pricing"))
Answers
-
Hi @otavilog,
This should do what you're after:
=COUNTIFS(Client:Client, OR(@cell = "A", @cell = "B"), Initiative:Initiative, <>"N/A", Progress:Progress, "Waiting for Pricing")
Hope this helps; if you've any problems or questions then just ask! 🙂
-
Thank you. I tried but since I am referencing an outside sheet. I dont think I got the syntax correct. Could you please advise?
=COUNTIFS({Client}:{ Client}, OR(@cell = "A", @cell = "B"), {Initiative}:{Initiative}, <>"N/A", {Progress}:{Progress}, "Waiting for Pricing")
-
For the cross sheet references, if your ranges are single columns then you can use { } round them, you don't need the range (e.g. A:A) like if they are were a column reference.
E.g. your client reference should be like this (you would need to rename it if you wanted it as that, though the formula will work fine with the default inserted instead):
So in this case:
=COUNTIFS({Client}, OR(@cell = "A", @cell = "B"), {Initiative}, <>"N/A", {Progress}, "Waiting for Pricing")
Hope this makes sense - if something is still not clear let me know.
-
@Nick Korna thank you again. One last question (i hope).
How would the syntax be if Client column cells also on an external sheet (A, B and C).
I tried following with no luck:
=COUNTIFS({Client}, = "A", OR({Client}, ="B")
and
=COUNTIFS({Client}, OR(@cell = "A", @cell = "B")
and
=COUNTIFS({Client}, = "A", OR( = "B")
Thank you in advance.
-
If you're just wanting a count of how many times clients A or B appear on a sheet, then it would be:
=COUNTIF({Client}, OR(@cell = "A", @cell = "B"))
You can add in extra ones by just extending the OR portion of the statement. For example, to add in "C":
=COUNTIF({Client}, OR(@cell = "A", @cell = "B", @cell = "C"))
I'm not 100% sure if this was quite what you mean, so apologies if I have misunderstood - if it isn't let me know!
-
Its my fault actually in my previous ask. I only posted half of the formula.
My original ask and the example table I shared are in an external sheet.
My current ask is how to make the formula work based on all the information being in an external sheet. Does this make sense (my apologies).
Regardless, I got it to work because of your input and guidance. Thank you so much.
-
Happy to help, if there is anything else just ask! 😊
-
I am cross referencing 2 different sheets getting an error can someone help.
=COUNTIF({Munis Range 1}, "Facilities", {Contract Extensions Range 1}, NOT("Expired", "Closed"))
I want it to count if the first range is "Facilities" and if the second range is NOT "expired" or "Closed"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!