Nested Function or COUNTIF...what will work?

[Stacey]

In column B, if the word “OTHER” appears in the cell, I want it to count the “x” in column E and display the result in C6. I can not determine what function will give me accurate results for what I need. Do I need to nest a function, or can I use just COUNTIF? Thank you!!!

 

[Aladin Akyurek]

=SUMPRODUCT(--($B$2:$B$100="OTHER"),--($E$2:$E$100="x"))

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I
1		OTHER			x
2		OTHER
3					x
4		OTHER			x
5
6			2
7
8
Sheet1

 

[Stacey]

WOW! That was a fast response! Mark, I tried the formula you mentioned and it didn't work for me...what exactly will I type in the cell that I want the result to appear? Thank you!

 

[Mark W.]

WOW! That was a fast response! Mark, I tried the formula you mentioned and it didn't work for me...what exactly will I type in the cell that I want the result to appear? Thank you!

The formula that I provided is an array formula. Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas...".

 

[Stacey]

Mark, that worked perfectly. I feel like I have struck GOLD with this site! Thank you.

 

[PaddyD]

A pivot table may also be appropriate, depending on how many conditions you have to track.