Beginner needs help with counting and formatting please

[Carl_London]

Hi!

I have a worksheet that needs to look like this:

Row 1 is the header row
Column C should show Good and format Green if it the first entry where the combination of Column A and B is disinct (is that the correct word?)
Column C should show Duplicate for any subsequent entries where the combination of Column A and B has already appeared higher
Column C should remain blank and not count where either or both of Column A or B are blank, I may need add additional rows

Cell C13 should count and display the total number of entries
Cell C14 should count and display the number of good entries
Cell C15 should count and display the number of duplicate entries

I am getting myself of a muddle, any help for this newbie would be very very welcome.

Thanks for any help!

Carl

 

[awoohaw]

try:

Mr excel questions 66.xlsm
	A	B	C
1	Name	Place	Status
2	GQDJ	NPOY	Good
3	QKPV	OEFW	Good
4	PZBE	FWHM	Good
5
6	QKPV	OEFW	Duplicate
7		ONMO
8	WWZB	TASD	Good
9	QKPV	OEFW	Duplicate
10	MFKX
11	QPEO	XJFB	Good
12
13		Total:	10
14		Good:	5
15		Duplicate:	2
Carl London

Cell Formulas
Range	Formula
C2:C11	C2	=IF(OR(A2="",B2=""),"",IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1,"Duplicate","Good"))
C13	C13	=COUNTA(C2:C11)
C14	C14	=COUNTIF(C2:C11,"Good")
C15	C15	=COUNTIF(C2:C11,"Duplicate")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C11,C13	Expression	=IF(OR(A2="",B2=""),"",IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1,"Duplicate","Good"))="Good"	text	NO
C2:C11,C13	Expression	=IF(OR(A2="",B2=""),"",IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1,"Duplicate","Good"))="Duplicate"	text	NO

 

[Carl_London]

Thank you very much Awoohaw! You're awesome

I could also do with a pointer on the totals at C13, C14, C15

Thanks again for you help,

Carl

 

[Carl_London]

Thank you, saved my day!

Best wishes, Carl

 

[awoohaw]

You're welcome, and thanks for the feedback.
Cell C13 uses the COUNTA function. COUNTA counts all cells that are not empty, a "" or a formula that results in "" is not empty.
Cell C14 and C15 use the same function I use in the Conditional Formatting and Formulas in the cells C2:C11. COUNTIF and COUNTIFS. It counts cells where only the criteria is/are met.

Best Wishes to you too!