Count # of items returned with multiple criteria

cnikkih

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!

Paul Newcome

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))

Leibel S

@cnikkih

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))

Paul Newcome

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)

Paul Newcome

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))

Leibel S

@cnikkih

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))

cnikkih

Thank you! Both of these answers worked.

cnikkih

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)

Paul Newcome

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?

cnikkih

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!

Paul Newcome

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)

cnikkih

THIS IS IT!! That did it. Thank you so much, Paul. I think we can call this one all the way done.

Paul Newcome

Happy to help. 👍️