Identify unique items in a column based on a specifc condition in another column to return 1 or zero

[dyanna]

Hello!
I’m trying to find a formula for an Excel 2010 workbook that will identify the number of unique ids in one column that meet a condition in a second column then returns a count of 1 in the cell for the first instance then 0 for each recurring instance. Ultimately the count columns will be used in a pivot table to summarize the results.

This sample chart shows a company code in column G and the negative number of days an invoice is due in column M.

In cell AC4 I’m using the formula =IF(SUMPRODUCT(($A$4:$A41=A41)*($G$4:$G41=G41))>1,0,1)which is copied down and works because the list only contains invoices <-1 days past due.



The formula in column AD needs to return 1 at the first invoice <-30 days then 0 for any recurring instance that meets that criteria. The formula in AE needs to return 1 at the first invoice <-90 days past due then 0 for any recurring instance that meets that criteria.

The values have been keyed in AD and AE just to demonstrate the desired result.

Thank you in advance for your help/advice!

[TABLE="width: 259"]
<tbody>[TR]
[TD="width: 22, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"]A
[/TD]
[TD="width: 42, bgcolor: transparent"]G
[/TD]
[TD="width: 63, bgcolor: transparent"]M
[/TD]
[TD="width: 59, bgcolor: transparent"]AC
[/TD]
[TD="width: 64, bgcolor: transparent"]AD
[/TD]
[TD="width: 60, bgcolor: transparent"]AE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]State
[/TD]
[TD="width: 42, bgcolor: transparent"]Comp Code
[/TD]
[TD="width: 63, bgcolor: transparent"]Days Past Due
[/TD]
[TD="width: 59, bgcolor: transparent"]# of Comp 1+ Days PD
[/TD]
[TD="width: 64, bgcolor: transparent"]# of Comp 30+ Days PD
[/TD]
[TD="width: 60, bgcolor: transparent"]# of Comp 90+ Days PD
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"] CA
[/TD]
[TD="bgcolor: transparent, align: right"]545
[/TD]
[TD="bgcolor: transparent, align: right"]-7
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"] CA
[/TD]
[TD="bgcolor: transparent, align: right"]545
[/TD]
[TD="bgcolor: transparent, align: right"]-15
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"] AZ
[/TD]
[TD="bgcolor: transparent, align: right"]842
[/TD]
[TD="bgcolor: transparent, align: right"]-35
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"] AZ
[/TD]
[TD="bgcolor: transparent, align: right"]842
[/TD]
[TD="bgcolor: transparent, align: right"]-95
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent"] AZ
[/TD]
[TD="bgcolor: transparent, align: right"]842
[/TD]
[TD="bgcolor: transparent, align: right"]-105
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent"]IL
[/TD]
[TD="bgcolor: transparent, align: right"]974
[/TD]
[TD="bgcolor: transparent, align: right"]-2
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent"]IL
[/TD]
[TD="bgcolor: transparent, align: right"]974
[/TD]
[TD="bgcolor: transparent, align: right"]-31
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent"]IL
[/TD]
[TD="bgcolor: transparent, align: right"]974
[/TD]
[TD="bgcolor: transparent, align: right"]-60
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent"]IL
[/TD]
[TD="bgcolor: transparent, align: right"]974
[/TD]
[TD="bgcolor: transparent, align: right"]-100
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Maybe something like

Excel 2013 32 bit
	B	C	D	E	F	G
1	State	Comp Code	Days Past Due	# of Comp 1+ Days PD	# of Comp 30+ Days PD	# of Comp 90+ Days PD
2	CA	545	-7	1	0	0
3	CA	545	-15	0	0	0
4	AZ	842	-35	1	1	0
5	AZ	842	-95	0	0	1
6	AZ	842	-105	0	0	0
7	IL	974	-2	1	0	0
8	IL	974	-31	0	1	0
9	IL	974	-60	0	0	0
10	IL	974	-100	0	0	1
Sheet2

Cell Formulas
Range	Formula
E2	=IF(COUNTIFS(C$2:C2,C2,D$2:D2,"<"&0)=1,1,0)
F2	=IF(COUNTIFS(C$2:C2,C2,D$2:D2,"<=-"&30)=1,1,0)
G2	=IF(COUNTIFS(C$2:C2,C2,D$2:D2,"<=-"&90)=1,1,0)

 

[dyanna]

Maybe something like

Excel 2013 32 bit

	B	C	D	E	F	G
State	Comp Code	Days Past Due	# of Comp 1+ Days PD	# of Comp 30+ Days PD	# of Comp 90+ Days PD
CA
CA
AZ
AZ
AZ
IL
IL
IL
IL

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]545[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]545[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]842[/TD]
[TD="align: right"]-35[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]842[/TD]
[TD="align: right"]-95[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]842[/TD]
[TD="align: right"]-105[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]974[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]974[/TD]
[TD="align: right"]-31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]974[/TD]
[TD="align: right"]-60[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]974[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]E2[/TH]
[TD="align: left"]=IF(COUNTIFS([COLOR=#0]C$2:C2,C2,D$2:D2,"<"&0)=1,1,0[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]F2[/TH]
[TD="align: left"]=IF(COUNTIFS([COLOR=#0]C$2:C2,C2,D$2:D2,"<=-"&30)=1,1,0[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G2[/TH]
[TD="align: left"]=IF(COUNTIFS([COLOR=#0]C$2:C2,C2,D$2:D2,"<=-"&90)=1,1,0[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

THANK YOU FLUFF!!! THANK YOU, THANK YOU, THANK YOU!!!!!!!! THIS WORKED BEAUTIFULLY and the response was so well presented that it was very easy to follow.

 

[Fluff]

Glad to help & thanks for the feedback

 

[dyanna]

Hello again!
I’m nowtrying to identify the number of unique ids in the Comp Code column that meet acondition in the Days Past Due column as well as meet/not meet a condition inthe Invoice Type-Service column. The results in columns F and G demonstrate thelocation of the formulas and expected results.
Specificallythe goal is to calculate
Column F: # of Comp 30+ Days PD (notService)
Column G: # of Comp 90+ Days PD-Service

Thecurrent formulas which do not include the Invoice type condition are:
[TABLE="width: 321"]
<tbody>[TR]
[TD="width: 80"] F2
[/TD]
[TD="width: 455, bgcolor: transparent"] =IF(COUNTIFS(C$2:C2,C2,D$2:D2,"<=-"&30)=1,1,0)
[/TD]
[/TR]
[TR]
[TD="width: 80"] G2
[/TD]
[TD="width: 455, bgcolor: transparent"] =IF(COUNTIFS(C$2:C2,C2,D$2:D2,"<=-"&90)=1,1,0)
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 555"]
<tbody>[TR]
[TD="width: 33, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"]C
[/TD]
[TD="width: 90, bgcolor: transparent"]D
[/TD]
[TD="width: 127, bgcolor: transparent"]E
[/TD]
[TD="width: 223, bgcolor: transparent"]F
[/TD]
[TD="width: 193, bgcolor: transparent"]G
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Comp Code
[/TD]
[TD="bgcolor: transparent"]Days Past Due
[/TD]
[TD="bgcolor: transparent"]Invoice Type Service
[/TD]
[TD="bgcolor: transparent"]# of Comp 30+ Days PD (not Service)

[/TD]
[TD="bgcolor: transparent"]# of Comp 90+ Days PD-Service
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]545
[/TD]
[TD="bgcolor: transparent"]-31
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]545
[/TD]
[TD="bgcolor: transparent"]-62
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]842
[/TD]
[TD="bgcolor: transparent"]-58
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5
[/TD]
[TD="bgcolor: transparent"]842
[/TD]
[TD="bgcolor: transparent"]-95
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]842
[/TD]
[TD="bgcolor: transparent"]-105
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7
[/TD]
[TD="bgcolor: transparent"]974
[/TD]
[TD="bgcolor: transparent"]-62
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"]974
[/TD]
[TD="bgcolor: transparent"]-31
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]974
[/TD]
[TD="bgcolor: transparent"]-92
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10
[/TD]
[TD="bgcolor: transparent"]974
[/TD]
[TD="bgcolor: transparent"]-100
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
</tbody>[/TABLE]



I apologize for the format. I’m prohibitedfrom using the tools referenced in the forum guidelines.

I’ve tried unsuccessfully to modify theseformulas to include the new column in the equation. Since column E may include a 1 or null, addinga third criterion to the COUNTIFS isn’t viable. I tried adding an IF/AND tomake the current formula the value if true which failed. Since these resultsneed to be represented in a pivot table, I can’t take the easy way out and separatethe data into two different lists – Service and Not Service.
Anyhelp is appreciated!

 

[Fluff]

I do not understand how you come to your expected answers, can you please explain.

 

[dyanna]

I do not understand how you come to your expected answers, can you please explain.

Yes, I’ll be more than happyto do so. Thank you for your response!

I think the last four rowsare the best for example purposes.
Company 974 has four pastdue invoices.
Row 7: The invoice is 62 DaysPast Due and Column E is blank which indicates this is not a Service invoice. Itmeets the criteria for 30+ Days Past Due (not service). Column F shouldcalculate 1. Column G should calculate 0.
Row 8: The invoice is 31Days Past Due and Column E has a 1 which indicates it is a Service invoice. Itdid not meet the criteria for 30+ Days Past Due (not service) or 90+ Days PastDue – Service. Columns F and G should calculate 0.
Row 9: The invoice is 92 DaysPast Due and Column E has a 1 which indicates it is a Service invoice. It meetsthe criteria for 90+ Days Past Due - Service. Column F should calculate 0.Column G should calculate 1.
Row 10: The invoice is 100Days Past Due and Column E is blank which indicates this is not a Serviceinvoice. It meets the criteria for 30+ Days Past Due (not service). However,this company has already been “counted” once for this category so Column Fshould calculate 0. Column G should calculate 0.


I appreciate any suggestions!

 

[Fluff]

Unfortunately, this is now beyond by formula abilities.
Hopefully somebody else will step in with an answer.

 

[dyanna]

Unfortunately, this is now beyond by formula abilities.
Hopefully somebody else will step in with an answer.

Thank you Fluff. I appreciate you for taking the time to look at. I think my pride would have been a little bruised if it was something simple that I was missing.
I do have a manual work around it just takes extra steps - separate the data into Service and Non-Service sheets. Use the wonderful formulas that you already gave me and bring the two pieces back together for the pivot table.
Thanks again!