# If Then Count Data in Range

#### jonisanders

##### New Member
I know this is probably easy and I had it working once but when I sorted the data I received FALSE in my result cell.

What I want to do is this:
if a cell in a range (a2:a101) equals to X then count the instances of data entered in (c2:e101).

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### schielrn

##### Well-known Member
Maybe something like?

=if(countif(a2:a101,"X"),counta(c2:e101),"No X Found")

Not sure what you mean by count the instances of data entered, but I assume you mean count any cell with something in it?

Hope that helps.

#### jonisanders

##### New Member
Yes. Any instance with data in it.
What I have is =IF(COUNTIF(A2:A101,"Jon Doe"),COUNTA(C2:E101))

This is counting all instances of data entry in C2:E101 not just the ones associated with Jon Doe.

Any suggestions?

#### schielrn

##### Well-known Member
So you are only wanting to count data on the rows that have john doe in column A?

Yes.

#### schielrn

##### Well-known Member

Excel Workbook
ABCDE
95
10
11
12john doeds
13
14
15john doess
16
17
18
19john doe0
20
21
Employees
#VALUE!
Entered with Ctrl+Shift+Enter

Change cell references to what you need. Notice it is an array formula and needs to be confirmed with control+shift+enter and not just enter.

Hope that helps.

#### jonisanders

##### New Member
Thanks so much. Took me a while to get it going cause I kept forgetting about entering the array but it works great now.
Thanks again.

Replies
8
Views
143
Replies
1
Views
253
Replies
1
Views
629
Replies
3
Views
243
Replies
10
Views
1K

1,190,841
Messages
5,983,187
Members
439,827
Latest member
JohnExcelHelp99

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back