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!

	A	G	M	AC	AD	AE
3	State	Comp Code	Days Past Due	# of Comp 1+ Days PD	# of Comp 30+ Days PD	# of Comp 90+ Days PD
4	CA	545	-7	1	0	0
5	CA	545	-15	0	0	0
6	AZ	842	-35	1	1	0
7	AZ	842	-95	0	0	1
8	AZ	842	-105	0	0	0
9	IL	974	-2	1	0	0
10	IL	974	-31	0	1	0
11	IL	974	-60	0	0	0
12	IL	974	-100	0	0	1

<tbody>
</tbody>

 

[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
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

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E2 =IF(COUNTIFS([COLOR=#0]C$2:C2,C2,D$2:D2,"<"&0)=1,1,0[/COLOR]) F2 =IF(COUNTIFS([COLOR=#0]C$2:C2,C2,D$2:D2,"<=-"&30)=1,1,0[/COLOR]) G2 =IF(COUNTIFS([COLOR=#0]C$2:C2,C2,D$2:D2,"<=-"&90)=1,1,0[/COLOR]) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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:

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)

<tbody> </tbody>

	C	D	E	F	G
1	Comp Code	Days Past Due	Invoice Type Service	# of Comp 30+ Days PD (not Service)	# of Comp 90+ Days PD-Service
2	545	-31		1	0
3	545	-62	1	0	0
4	842	-58		1	0
5	842	-95		0	0
6	842	-105	1	0	1
7	974	-62		1	0
8	974	-31	1	0	0
9	974	-92	1	0	1
10	974	-100		0	0

<tbody>
</tbody>

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!