Question about using If and Index

[Dhurl]

Hi,

I'm having an issue with counting words across columns. I'm looking for a solution using multiple index formulas

[TABLE="width: 500"]
<tbody>[TR]
[TD]Team 1[/TD]
[TD]Team 2[/TD]
[TD]Team 3[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keep[/TD]
[TD][/TD]
[TD][/TD]
[TD]=INDEX(A2:C2,1,1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Delete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Keep[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Keep[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the case above Index selects 'Keep' but I'm looking for a formula that will pick the word from each row into the total.

Can anyone advise?

 

[AlanY]

if you wanted to count them per row

Excel 2012
	A	B	C	D
1	Team 1	Team 2	Team 3	Total
2	Keep			1
3		Delete		0
4		Keep		1
5			Keep	1
Sheet6

Cell Formulas
Range	Formula
D2	=COUNTIF(A2:C2,"keep")

 

[Dhurl]

just to update this, I have my formula pretty close but am struggling with the 'True' value at the end

=IF(OR(A2=INDEX(A2:C2,1,1),B2=INDEX(A2:C2,1,2),C2=INDEX(A2:C2,1,3)),"","")

so what I want is for the formula to return either "Keep" or "Delete" as the 'true' value and then blank as the 'False' value

 

[steve the fish]

Maybe this?

=INDEX(A2:C2,MATCH(TRUE,INDEX(A2:C2<>"",0),0))

 

[AlanY]

if only optios in the cells are keep, delete and empty, then

=IF(COUNTBLANK(A2:C2)=3,"False","True")

should do it

 

[steve the fish]

This A2=INDEX(A2:C2,1,1) will always be true because essentially its saying A2=A2.

 

[Dhurl]

that worked perfect, thanks!