Countifs (duplicates counted ones)

[taxx411]

I have 3 columns

Full Name, Status, Source

Need a count ifs function to count my leads without duplicating in column A (names)

Existing Formula: =COUNTIFS(G:G,"*Open - Lead*",J:J,"Opcity")

Any ideas on how I can fix this?

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[taxx411]

here is a sample

 

[taxx411]

I can't figure out how to paste it here

 

[Fluff]

Have you downloaded & installed the Xl2BB add-in?

 

[taxx411]

Have you downloaded & installed the Xl2BB add-in?

Downloaded file won't open

 

[Fluff]

Did you follow the instructions in the link I supplied?
If so does the add-in appear in the add-ins list in Excel?

 

[taxx411]

Ok I had to close out of my Excel for the file to open. Now it won't detect my ranges so i cannot copy

 

[taxx411]

I have 3 columns

Full Name, Status, Source

Need a count ifs function to count my leads without duplicating in column A (names)

Existing Formula: =COUNTIFS(G:G,"*Open - Lead*",J:J,"Opcity")

Any ideas on how I can fix this?

Here it is

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Email Auto Pilot				Open - Lead (Opcity)	-
2		Email Alex Abba only				Open - Lead (Zillow)	4.00	Should be 3 as buyer A is the same person
3						Open - Lead (David Yakubov)	-
4	MLS #	Full Name	Phone 1	E-Mail	Appointment	Address	Status	Amount	Lead Date	Source	Bank Letter	Agency	Fair Housing	Covid	Notes
5		Buyer A	(999) 999-9999	buyera@aol.com		77-20 Austin Street, Forest Hills, NY 11375 Unit #:6D	Open - Lead	$ 319,000.00	20-Nov-21	Zillow	No	No	No	No
6		Buyer A	(999) 999-9999	buyera@aol.com		67-87 Booth Street, Forest Hills, NY 11375 Unit #:3H	Open - Lead	$ 338,000.00	21-Nov-21	Zillow	No	No	No	No
7		Buyer C	(999) 999-9999	buyerc@aol.com		68-09 Booth St #4N, Forest Hills, NY 11375	Open - Lead	$ 324,999.00	21-Nov-21	Zillow	No	No	No	No
8	3342994	Buyer D	(999) 999-9999	buyerd@aol.com		67-71 Yellowstone Boulevard, Forest Hills, NY 11375 Unit #:7F	Open - Lead		21-Nov-21	Zillow	No	No	No	No
Sales Lead Sheet

Cell Formulas
Range	Formula
G1	G1	=COUNTIFS(G:G,"*Open - Lead*",J:J,"Opcity")
G2	G2	=COUNTIFS(G:G,"*Open - Lead*",J:J,"Zillow")
G3	G3	=COUNTIFS(G:G,"*Open - Lead*",J:J,"David Yakubov")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Cell Value	contains "Seller - Lead"	text	NO
G:G	Cell Value	contains "Open - Lead"	text	NO
G:G	Cell Value	="Dead"	text	NO
G:G	Cell Value	="Expired"	text	NO
J:J	Cell Value	="Opcity"	text	NO
J:J	Cell Value	="David Yakubov"	text	NO
J:J	Cell Value	="Zillow"	text	NO
J5:J8	Cell Value	="Door Knocking"	text	NO
J5:J8	Cell Value	="Opcity"	text	NO
K5:N1048576	Cell Value	contains "Yes"	text	NO
K5:N1048576	Cell Value	contains "No"	text	NO

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Email Auto Pilot				Open - Lead (Opcity)	0
2		Email Alex Abba only				Open - Lead (Zillow)	3	Should be 3 as buyer A is the same person
3						Open - Lead (David Yakubov)	0
4	MLS #	Full Name	Phone 1	E-Mail	Appointment	Address	Status	Amount	Lead Date	Source	Bank Letter	Agency	Fair Housing	Covid	Notes
5		Buyer A	9999999999	buyera@aol.com		77-20 Austin Street, Forest Hills, NY 11375 Unit #:6D	Open - Lead	319000	44520	Zillow	No	No	No	No
6		Buyer A	9999999999	buyera@aol.com		67-87 Booth Street, Forest Hills, NY 11375 Unit #:3H	Open - Lead	338000	44521	Zillow	No	No	No	No
7		Buyer C	9999999999	buyerc@aol.com		68-09 Booth St #4N, Forest Hills, NY 11375	Open - Lead	324999	44521	Zillow	No	No	No	No
8	3342994	Buyer D	9999999999	buyerd@aol.com		67-71 Yellowstone Boulevard, Forest Hills, NY 11375 Unit #:7F	Open - Lead		44521	Zillow	No	No	No	No
9
Main

Cell Formulas
Range	Formula
G1	G1	=IFERROR(ROWS(UNIQUE(FILTER($B$5:$B$100,($G$5:$G$100="open - lead")*($J$5:$J$100="opcity")))),0)
G2	G2	=IFERROR(ROWS(UNIQUE(FILTER($B$5:$B$100,($G$5:$G$100="open - lead")*($J$5:$J$100="zillow")))),0)
G3	G3	=IFERROR(ROWS(UNIQUE(FILTER($B$5:$B$100,($G$5:$G$100="open - lead")*($J$5:$J$100="David Yakubov")))),0)