Count # of items returned with multiple criteria
We need to count the number of items returned based on the following criteria:
ColumnA can be one of 3 options (Apple, Banana, Chocolate), but not the other 3 options (Donut, Eclair, Fruit).
ColumnB must be greater than April 1, 2023.
I have tried CountIfs, CountM, nested CountIfs, SumIfs, helper columns, etc. and I have reached that point where none of it makes sense anymore. Help!
Best Answers
-
You will need a COUNTIFS with an OR statement.
=COUNTIFS(ColumnA:ColumnA, OR(@cell = "Apple", @cell = "Banana", @cell = "Chocolate"), ColumnB:ColumnB, @cell> DATE(2023, 04, 01))
-
Try the below:
=COUNTIFS(ColumnA:ColumnA, OR(HAS(@cell, "Apple"), HAS(@cell, "Banana"), HAS(@cell, "Chocolate")), ColumnB:ColumnB, @cell > DATE(2023, 4, 1))
If columnA can have apple and donut for example, and you don't want it counted then:
=COUNTIFS(ColumnA:ColumnA, AND(OR(HAS(@cell, "Apple"), HAS(@cell, "Banana"), HAS(@cell, "Chocolate")), NOT(OR(HAS(@cell, "Donut"), HAS(@cell, "Eclair"), HAS(@cell, "Fruit")))), ColumnB:ColumnB, @cell > DATE(2023, 4, 1))
-
Give this a try:
=COUNTIFS([Asset Technology Stack]:[Asset Technology Stack], OR(@cell = "Application", @cell = "SaaS", @cell = "PaaS"), [Asset ID]:[Asset ID], VALUE(@cell) > 434)
Answers
-
You will need a COUNTIFS with an OR statement.
=COUNTIFS(ColumnA:ColumnA, OR(@cell = "Apple", @cell = "Banana", @cell = "Chocolate"), ColumnB:ColumnB, @cell> DATE(2023, 04, 01))
-
Try the below:
=COUNTIFS(ColumnA:ColumnA, OR(HAS(@cell, "Apple"), HAS(@cell, "Banana"), HAS(@cell, "Chocolate")), ColumnB:ColumnB, @cell > DATE(2023, 4, 1))
If columnA can have apple and donut for example, and you don't want it counted then:
=COUNTIFS(ColumnA:ColumnA, AND(OR(HAS(@cell, "Apple"), HAS(@cell, "Banana"), HAS(@cell, "Chocolate")), NOT(OR(HAS(@cell, "Donut"), HAS(@cell, "Eclair"), HAS(@cell, "Fruit")))), ColumnB:ColumnB, @cell > DATE(2023, 4, 1))
-
Thank you! Both of these answers worked.
-
Hopefully this still gets seen even though I marked it complete... We just now decided to go a different route with our count. Now it needs to be:
Asset Technology Stack can be one of 3 options (Apple, Banana, Chocolate), but not the other 3 options (Donut, Eclair, Fruit). <-- Last column in the attached image.
Asset ID must be greater than 0434. <-- First column in the attached image.
I'm using the following formula but it returns 0 rows. It should be returning 5 (all the Apples and Bananas). Is there something about the Asset ID field that it can't count? It is an Auto number field.
=COUNTIFS([Asset Technology Stack]:[Asset Technology Stack], OR(@cell = "Application", @cell = "SaaS", @cell = "PaaS"), [Asset ID]:[Asset ID], @cell > 0434)
-
The issue is with the Asset ID. The leading zero converts it to a text string and as such can't be used in a greater than/less than comparison. Do you have any Asset IDs that do not start with a leading zero?
-
No, they are all formatted this way. I figured that might be the case, and I think I will just go back to the initial formula and leave it be. Thank you again!
-
Give this a try:
=COUNTIFS([Asset Technology Stack]:[Asset Technology Stack], OR(@cell = "Application", @cell = "SaaS", @cell = "PaaS"), [Asset ID]:[Asset ID], VALUE(@cell) > 434)
-
THIS IS IT!! That did it. Thank you so much, Paul. I think we can call this one all the way done.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!