formula to find if value meets criteria in another column

[annielise]

Hi all,

I'm having trouble figuring out a formula in excel, and was hoping someone may be able to assist.

I have data for a number of people and the status of their work in another column. Essentially want to calculate an overall status. If they have atleast one work complete in 2018, then 'Complete in 2018' if not, see if they have any In Progress in 2018 then 'Incomplete in 2018', if not then 'No work in 2018'.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Status
[/TD]
[TD]Date
[/TD]
[TD]Overall
[/TD]
[/TR]
[TR]
[TD]Andrew
[/TD]
[TD]In Progress
[/TD]
[TD]2018
[/TD]
[TD]Complete in 2018
[/TD]
[/TR]
[TR]
[TD]Andrew
[/TD]
[TD]In Progress
[/TD]
[TD]2018
[/TD]
[TD]Complete in 2018
[/TD]
[/TR]
[TR]
[TD]Andrew
[/TD]
[TD]Complete[/TD]
[TD]2018
[/TD]
[TD]Complete in 2018
[/TD]
[/TR]
[TR]
[TD]Anna
[/TD]
[TD]In Progress
[/TD]
[TD]2018
[/TD]
[TD]Incomplete in 2018
[/TD]
[/TR]
[TR]
[TD]Anna
[/TD]
[TD]Complete
[/TD]
[TD]2017
[/TD]
[TD]Incomplete in 2018
[/TD]
[/TR]
[TR]
[TD]Kate
[/TD]
[TD]Complete
[/TD]
[TD]2017
[/TD]
[TD]No work in in 2018
[/TD]
[/TR]
[TR]
[TD]Joseph
[/TD]
[TD]In Progress
[/TD]
[TD]2018
[/TD]
[TD]Complete in 2018
[/TD]
[/TR]
[TR]
[TD]Joseph
[/TD]
[TD]Complete
[/TD]
[TD]2018
[/TD]
[TD]Complete in 2018
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Annie

 

[mrshl9898]

=IF(COUNTIFS(A:A,A2,B:B,"Complete",C:C,2018)>0.1,"Complete in 2018",IF(COUNTIFS(A:A,A2,B:B,"In Progress",C:C,2018)>0.1,"Complete in 2018","No Work in 2018"))

 

[jtakw]

Hi,

Also try:

Book1
	A	B	C	D
1	Name	Status	Date	Overall
2	Andrew	In Progress	2018	Complete in 2018
3	Andrew	In Progress	2018	Complete in 2018
4	Andrew	Complete	2018	Complete in 2018
5	Anna	In Progress	2018	Incomplete in 2018
6	Anna	Complete	2017	Incomplete in 2018
7	Kate	Complete	2017	No work in 2018
8	Joseph	In Progress	2018	Complete in 2018
9	Joseph	Complete	2018	Complete in 2018
Sheet240

Cell Formulas
Range	Formula
D2	=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"Complete",C$2:C$9,2018),"Complete",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"In Progress",C$2:C$9,2018),"Incomplete","No work"))&" in 2018"

Change/adjust cell references/range to suit your data, formula copied down.