Create list of data meeting multiple criteria

[vgresia]

I have two issues that I am looking for help with.

The first, is to create a dynamic list of results for items matching "Horse" in column J on Sheet 2!, "Ball" in column I on Sheet 2!, and that do not contain the word dog in column A on Sheet 2!

The second, is that while I have calculated the number of assets meeting this criteria, using

=COUNTIFS(Sheet2!$A:$A, "<>*Dog*", Sheet2!$J:$J, "Horse", Sheet2!$I:$I, "Ball")

I would like to calculate the number of unique entries as well.

Thank you!

 

[Logit]

.
https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

 

[vgresia]

Yes, I had seen this but unfortunately cannot get the formulas there to give me the correct answer. I tried =SUM(--(FREQUENCY(IF((Sheet2!$A$2:$A$10000<>"*Dog*")*(Sheet2!$J$2:$J$10000="Horse"),Sheet2!$C$2:$C$10000),Sheet!$C$2:$C$10000)>0))

but is it giving me an answer that is incorrect.

 

[vgresia]

To clarify, I am looking to count the number of unique items that are in column C.

 

[AhoyNC]

Maybe something like this:
Also, the IF function doesn't support wildcards, which is why I used the SEARCH function.

This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	H	I	J
1	Header1		Header4		Header2	Header3
2	Test		item4		Ball	Horse
3	Dog Test		item1		Ball	Horse
4	Cat		item2		Ball	test
5	Sam		item1		Ball	Horse
6	Test Dog		item3		Ball	Horse
7	Bill		item2		Ball	Horse
8	sam		item6		Ball	Bird
9
10	3
Sheet

 

[vgresia]

This worked perfectly, thank you! Good to know about wildcards and IF, I didn't know that. If I wanted to omit the Ball part of the formula, would I just remove IF($I$2:$I$8="Ball" or is there anything else I would need to remove as well?

 

[AhoyNC]

You're welcome. Yes to remove "Ball" just remove that IF statement.
Which would become:

=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH("Dog",$A$2:$A$8)))=0,IF($J$2:$J$8="Horse",MATCH($C$2:$C$8,$C$2:$C$8,0))),ROW($C$2:$C$8)-ROW($C$2)+1),1))