If statement that considers duplicate entries

[Jenbot]

I need to write a formula that returns one count for every unique index number that either shows:

If there are duplicates: overall status = "new" but all subgroup status for that index = "finished" (like for Index 456)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]overall status[/TD]
[TD]subgroup status[/TD]
[TD]Index Count[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]new[/TD]
[TD]canceled[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]new[/TD]
[TD]finished[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]new[/TD]
[TD]finished[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]new[/TD]
[TD]finished[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]new[/TD]
[TD]finished[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]new[/TD]
[TD]new[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]new[/TD]
[TD]finished[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas? My boss wants one formula, so I don't think conditional formatting will help here. I'm totally stumped on how to confirm duplicates and then run an if statement around a group of duplicates.

 

[DRSteele]

This formula reports a 1 in the Index Count field when the all of the items in that record's Index have Overall Status as 'new' and Subgroup Status as 'finished'; it reports a 0 when they are not all 'new' and 'finished'. Note that the formula will report a 1 for all records that match the criteria, not just the first occurrence.

Copy the properly invoked formula in D2 downwards for each record.

	A	B	C	D
new	cancelled
new	finished
new	finished
new	finished
new	finished
new	new
new	finished

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Index[/TD]
[TD="bgcolor: #FFF2CC"]Overall Status[/TD]
[TD="bgcolor: #FFF2CC"]Subgroup Status[/TD]
[TD="bgcolor: #FFF2CC"]Index Count[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

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

[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

</tbody>

Sheet39​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(SUM((A2=$A$2:$A$8)*($B$2:$B$8="new")*($C$2:$C$8="finished"))=SUM((A2=$A$2:$A$8)*($B$2:$B$8="new")),1,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]