COUNTIFS and EXACT

[whitebuffalofarm]

This should be simple, but i'm having trouble getting it to understand me.

Column L is all i'm working on, and when i get it i think i can edit it to fit.

What i want to ask.. COUNTIF any cell in column K has the same text as K4, AND also has the word "out" in column F.

Excel Workbook
	F	G	H	I	J	K	L
4	out					FLY	#VALUE!
5	out					FLY	2
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F4	=IF(LEFT($B4, 6)="POSTED", "", IF($E4="picked", SUBSTITUTE(IF(MID($B4,FIND(" to ", $B4)+4,LEN($B4))="underperform.","","out"),"outperform","out"), ""))
F5	=IF(LEFT($B5, 6)="POSTED", "", IF($E5="picked", SUBSTITUTE(IF(MID($B5,FIND(" to ", $B5)+4,LEN($B5))="underperform.","","out"),"outperform","out"), ""))
G4	=IF(LEFT($B4, 6)="POSTED", "", IF($E4="picked", SUBSTITUTE(IF(MID($B4,FIND(" to ", $B4)+4,LEN($B4))="outperform.","","under"),"underperform","under"), ""))
G5	=IF(LEFT($B5, 6)="POSTED", "", IF($E5="picked", SUBSTITUTE(IF(MID($B5,FIND(" to ", $B5)+4,LEN($B5))="outperform.","","under"),"underperform","under"), ""))
H4	=IF(LEFT($B4, 6)="POSTED", "", IF(ISNUMBER(SEARCH("ended",$B4)), "ended", ""))
H5	=IF(LEFT($B5, 6)="POSTED", "", IF(ISNUMBER(SEARCH("ended",$B5)), "ended", ""))
I4	=IF(LEFT($B4,6)="Posted ","",IF($H4="ended",SUBSTITUTE(IF(MID($B4,FIND(" ended ",$B4,1)+7,5)="under","","out"),"outpe","out"),""))
I5	=IF(LEFT($B5,6)="Posted ","",IF($H5="ended",SUBSTITUTE(IF(MID($B5,FIND(" ended ",$B5,1)+7,5)="under","","out"),"outpe","out"),""))
J4	=IF(LEFT($B4,6)="Posted ","",IF($H4="ended",SUBSTITUTE(IF(MID($B4,FIND(" ended ",$B4,1)+7,5)="under","under",""),"outpe",""),""))
J5	=IF(LEFT($B5,6)="Posted ","",IF($H5="ended",SUBSTITUTE(IF(MID($B5,FIND(" ended ",$B5,1)+7,5)="under","under",""),"outpe",""),""))
K4	=IF(LEFT($B4, 6)="POSTED", "", IF($E4="picked", MID($B4, FIND("^",SUBSTITUTE($B4, " ", "^", 2))+1, 3), SUBSTITUTE(MID($B4,FIND(" on ", $B4)+4,LEN($B4)),".","")))
K5	=IF(LEFT($B5, 6)="POSTED", "", IF($E5="picked", MID($B5, FIND("^",SUBSTITUTE($B5, " ", "^", 2))+1, 3), SUBSTITUTE(MID($B5,FIND(" on ", $B5)+4,LEN($B5)),".","")))
L4	=COUNTIFS(K:K,EXACT($K4,K:K),(Sheet1!F$4:F$1048576),"out")

 

[Aladin Akyurek]

Care to post a small sample along with the desired results without any formulas?

 

[whitebuffalofarm]

sure, Aladin Akyurek, do you mean like this?

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P
3
4	picked	out					FLY	#VALUE!				2,0
5	picked	out					FLY	2				2,0
6	picked	out					C t	1				1,0
7				ended	out		C		-1			-1,0
8				ended	out		LDK		-1			-1,0
9				ended	out		YGE		-1			-1,0
10	picked		under				COY			1		0,1
Sheet1

Excel 2007

Again, i think if i had the syntax for L4 i could edit most of the rest.

 

[T. Valko]

sure, Aladin Akyurek, do you mean like this?

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P
3
4	picked	out					FLY	#VALUE!				2,0
5	picked	out					FLY	2				2,0
6	picked	out					C t	1				1,0
7				ended	out		C		-1			-1,0
8				ended	out		LDK		-1			-1,0
9				ended	out		YGE		-1			-1,0
10	picked		under				COY			1		0,1
Sheet1

Excel 2007

Again, i think if i had the syntax for L4 i could edit most of the rest.

What i want to ask.. COUNTIF any cell in column K has the same text as K4, AND also has the word "out" in column F.

Try one of these...

This formula will work in any version of Excel.

=SUMPRODUCT(--(F4:F10="out"),--(K4:K10=K4))

This formula will work in Excel versions 2007 and later.

=COUNTIFS(F4:F10,"out",K4:K10,K4)

 

[whitebuffalofarm]

Thanks, T. Valko. I was able to edit the rest of the cells myself. Almost done!

 

[T. Valko]

Thanks, T. Valko. I was able to edit the rest of the cells myself. Almost done!

You're welcome. Thanks for the feedback!