CODEWITHSUNDEEP
excelexcel-formula
Vijaya
Vijaya

Reputation: 564

Count based on other column

I have a excel file which is having sorted data( Based on Object column) as below. Sample excel data

My requirement is to count object completion status based on the cell value in the column “Fixed/Not”.

For eg: if “Obj1” has the value as “Yes” in all rows, then only I need to consider as completed ,if any one row has value “No” I should not consider that object as completed.

Being new to Excel, I couldn’t find solution for that. Can anyone please suggest me how I can achieve this.

Thanks Vijaya

Upvotes: 0

Views: 59

Answers (1)

Youbaraj Sharma
Youbaraj Sharma

Reputation: 1295

Why don't you break it down into something like this, its make it lot easier to accommodate any additions to object list tomorrow. I have used sumproduct but you can very well use countif if you so desire

enter image description here

enter image description here

Formulae

Total To be Completed =SUMPRODUCT(($A$2:$A$7=$E2)*($A$2:$A$7>0))

Total Completed =SUMPRODUCT(($A$2:$A$7=$E2)*($C$2:$C$7="Yes")*($A$2:$A$7>0))

Final Count =IF(AND(F2<>0,F2=G2),1,0)

Upvotes: 1

Related Questions