Count multiple criteria across multiple columns - excluding certain values

[Jnehra]

Hello Forum

I am having a hard time with what I think should be a simple formula. I want to count the number of occurrences in one column excluding those that also meet a certain criteria in another column. Looking at the attached worksheet, I want to count the number of time BILLING appears in column B, but only those instances where DISABLED - Canceled does not appear in column AI. Please see screenshot below - I have also attached the worksheet for reference. So in the screenshot below showing rows 1-10 with a header in row 1, the formula would return a count of 7 for BILLING since rows 7&9 also contain DISABLED - Canceled in column AI.

Like I said, this seems to be an easy ask but I am having a hard time getting a formula that works. I appreciate any help!

Sample.PNG

[Limor_OP]

Hi,
Try this:
=COUNTIFS(B3:B103,"Billing",AH3:AH103,"<>"&"disabled - canceled")

[Jnehra]

That's great Belinda! Thank you...

[Limor_OP]

you're welcome Jnehra.

[Jnehra]

The solution you provided worked Belinda, but I have reopened the thread for a follow-up question to the forum.

Is there a way (FORMULA) to return a list of all the tasks in column F that meet the above criteria? For example, DATAFEED returned 4 matches under the following criteria:

TYPE: DATAFEED (column B), MATCH: MATCH (column AJ), DMZ OPTION: Does Not Include DISABLED - Cancelled (column AI).
=COUNTIFS(B3:B103,"Datafeed",AJ3:AJ103,"Match",AI3:AI103,"<>"&"disabled - canceled")

S6-LA_PE_D-127-OUT
S6-LA_PE_D-130-OUT
S6-LA_PE_D-72-OUT-v3.2
S6-LA_PE_D-74-OUT-v3.2

[Jnehra]

I was able to get an accurate list using a PIVOT TABLE, just thought I'd ask if there was a way to do this via a formula.

[Limor_OP]

Hi Jnehra,
are you still using excel 2010?

In 365 it would be possible with :
=FILTER(AH3:AH103,(B3:B103="Billing")*(AH3:AH103<>"disabled - canceled"))

(given in column J)

you can also wrap it with UNIQUE function to get each value only once:
=UNIQUE(FILTER(AH3:AH103,(B3:B103="Billing")*(AH3:AH103<>"disabled - canceled")))

(given in column k)

[Limor_OP]

If you are using older version try this:

=INDEX($AH$1:$AH$103,AGGREGATE(15,6,ROW($AH$3:$AH$103)/(($B$3:$B$103="Billing")*($AH$3:$AH$103<>"disabled - canceled")),ROWS($A$1:A1)))

[Jnehra]

Excellent! Thanks again Belinda. I have Office 365 so I'll play around with this a bit to see if it is any easier than the pivot table.

[Limor_OP]

you're welcome Jnehra
It is recommended to update your profile as well to 365, so you can get the best solution from anyone who offers help.