# Nested Function or COUNTIF...what will work?

This is a discussion on Nested Function or COUNTIF...what will work? within the Excel Questions forums, part of the Question Forums category; In column B, if the word “OTHER” appears in the cell, I want it to count the “x” in column ...

1. ## Nested Function or COUNTIF...what will work?

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!!!

2. ## Re: Nested Function or COUNTIF...what will work?

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

3. ## Re: Nested Function or COUNTIF...what will work?

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 C6 =

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 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

4. 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!

5. Originally Posted by 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!
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...".

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•