Count unique values with criterias

Hi,

I am hoping someone can help me with this, I have spent too much time trying variations, looking at a ton of sites looking for something that can get me to where I need to be.

I have three columns, each of which could contain a variety of choices and there could be duplicates

Here is some sample data: (Titles of columns are the named ranges being used)

Inv1Status Inv1Source Inv1Case Inv1Sub
----------- ------------- ------------- -----------
Active Internet Red Joe
Adjudicated Internet Red John
Warrant Self Tree Yogi
Active LE Car Han Solo
Inactive Self House Cathy
Inactive Self House Steve


What I am trying to do is count how many cases are not Adjudicated or Active for each Source type.

I have this array: =SUM(IFERROR(1/COUNTIFS(Inv1Case,Inv1Case&"",Inv1Source,N$2),0)) which gives me a correct count for the number of cases for each Source type. How do I now take it to the next level and have it exclude any Cases that have any 1 row which reflects Adjudicated or Inactive?

The expected result from the above would be
Internet = 0 (because row 2 has status of Adjudicated)
LE = 1
Self = 1 (ignores or doesn't count the case labeled House as it is Inactive)

I cannot help to think I am close, but just cannot get the right result once I start trying to bring in the conditions from the Inv1Status column.

Thank you in advance for your help.

edit: Sorry, it condensed the sample data when I submitted it.

Hello szc55. Welcome to the forum.

What is in cell N2?

=SUM(IFERROR(1/COUNTIFS(Inv1Case,Inv1Case&"",Inv1Source,N$2),0))

Hi, thank you for the quick response. That was a reference to another cell, which contained a Source, such as "Self". That can be changed. Can disregard that cell reference.

=SUM(IFERROR(1/COUNTIFS(Inv1Case,Inv1Case&"",Inv1Source,"Self"),0))

Is there a way I can fix my sample data above to better space things out instead of being compressed together, just to make it look better?

Also, thank you for the welcome. I joined a while ago and have been learning a lot, but this is one that has me stumped a little.

Yes.
Use the text to columns feature under the Data ribbon ... Data > Data tools > Text to columns.

I may have made a little breakthrough, but still not all the way there. The formula I put in above provides a unique total of Inv1Case for the choice of Inv1Source.

=SUM(IFERROR(1/COUNTIFS(Inv1Case,Inv1Case&"",Inv1Source,"Self"),0))

I put this together and it seems to be calculating the number of Inv1Cases for the Inv1Source of "Self" where there is at least one Inv1Sub that has an Inv1Status of Adjudicated:

=SUMPRODUCT(((Inv1Source="Self")*(Inv1Status="Adjudicated"))/(COUNTIFS(Inv1Case,Inv1Case,Inv1Source,"Self",Inv1Status,"Adjudicated")+(Inv1Source<>"Self")+(Inv1Status<>"Adjudicated"))) then Ctrl-Shift-Enter

With this, I now have other questions:

1) How can I incorporate the Inv1Status of "Inactive" into this second formula (so the formula totals both statuses of Adjudicated and Inactive?)
2) How can I subtract the second formula from the first one and have it be all one formula (would prefer to not use helper rows and not sure how to handle that subtraction in an array formula)

I hope you experts can help me get this and again, I thank you in advance for your help and time (nothing like spending time on a puzzle during quarantine )

There are instructions at the top of the page explaining how to attach your sample workbook.

Attached is a sample worksheet. In creating this, I found that the second formula I posted does not appear to be working properly, but I did notate that within the attached.

Edit: The second formula actually does appear to be working properly, it is totaling the unique cases for each source that reflect at least one Sub with an Adjudicated status. (sorry for any confusion)

Thank you!


EDIT: I tried to use the second formula twice, once for Status of Inactive and once for Status of Adjudicated, placing them into their own cells thinking I could subtract both results from the first formula. This does not work as one Inv1Case could have multiple Inv1Subs where one in Inactive and one is Adjudicated, and each formula then counts them both for the same case. When the subtraction then happens, it is subtracting two from the total when it should only be one.

Somehow it needs to know to ignore subsequent Inactive or Adjudicated statuses once 1 is found for a particular Inv1Case.

Is there a way to have the formula look at the Status as being an OR? Inactive or Adjudicated? Would that work?