# Count IF function and matching criteria in different columns

#### maggiec27

##### Board Regular
Hi I'm trying to count the number of cells in a range if the specified cell in a different range is matching specific criteria. In the example below i want to count the number of cells in column b if column a= 1234. I'm currently using a countif formula but getting the error that too many arguments are used, if i change it to countifs then it says i'm using too few arguments. My formula is COUNTIF(A1:A4, "1234", B1:B4). I'm new to this so i could be completely wrong on what i'm trying to do here. Thanks!

 A B 1 1234 Yes 2 123 No 3 1234 Yes 4 12345 Yes

<tbody>
</tbody>

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Dallin

##### New Member
Why would you want to count the cells in column B rather than A, when you're qualifying the count by the data in A? ie, if 1234 is listed twice in column A, it sounds like you want the result to be "2". If that's true: =countif(a1:a4,1234)... which will equal 2.

If you're not looking for the result to be "2", please clarify your desired result.

Last edited:

#### maggiec27

##### Board Regular
Yes I want the end result to be two. I'm wanting to use the count as a denominator in a fraction i'm using but I actually need to the count of the cells in column B that are not blank and match 1234 in column A.

#### Dallin

##### New Member
=countifs(a1:a4,1234,b1:b4,"<>")

#### CheryBTL

##### New Member
IF b is text.
=countifs(a1:a4,1234,b1:b4,"*")
or:
=Sumproduct((a1:a4=1234)*(b1:b4<>""))

#### maggiec27

##### Board Regular
=countifs(a1:a4,1234,b1:b4,"<>")

I'm using this formula and it's working well except for it's counting all cells in the range- even the blank ones.

The problem i'm running into is that all of my cells in my range have an NA error (b/c the data is not yet available) so to remove NA I used an (=IFNA, "") formula. So the cells look blank because of my formula but really there is an N/A error "hidden" on them. it seems to be affecting my count and i'm not sure how to exclude it. The formula i'm trying is this:

=(COUNTIFS(DZ\$3:DZ\$2668,\$E\$3187,E\$3:E\$2668,\$D3187))/(COUNTIFS(\$E\$3:\$E\$2668,\$D3187,DZ\$3:DZ\$2668,"<>"&"#N/A"))

#### CheryBTL

Try Sumproduct.

##### MrExcel MVP
Hi I'm trying to count the number of cells in a range if the specified cell in a different range is matching specific criteria. In the example below i want to count the number of cells in column b if column a= 1234. I'm currently using a countif formula but getting the error that too many arguments are used, if i change it to countifs then it says i'm using too few arguments. My formula is COUNTIF(A1:A4, "1234", B1:B4). I'm new to this so i could be completely wrong on what i'm trying to do here. Thanks!

 A B 1 1234 Yes 2 123 No 3 1234 Yes 4 12345 Yes

<TBODY>
</TBODY>

This would be, assuming that we are interested in the non-null text values in the B-range
Rich (BB code):
``````=COUNTIFS(\$A\$1:\$A\$4,1234,\$B\$1:\$B\$4,"?*")
``````

I'm using this formula and it's working well except for it's counting all cells in the range- even the blank ones.

The problem i'm running into is that all of my cells in my range have an NA error (b/c the data is not yet available) so to remove NA I used an (=IFNA, "") formula. So the cells look blank because of my formula but really there is an N/A error "hidden" on them. it seems to be affecting my count and i'm not sure how to exclude it. The formula i'm trying is this:

=(COUNTIFS(DZ\$3:DZ\$2668,\$E\$3187,E\$3:E\$2668,\$D3187))/(COUNTIFS(\$E\$3:\$E\$2668,\$D3187,DZ\$3:DZ\$2668,"<>"&"#N/A"))

Looks like:
Rich (BB code):
``````=COUNTIFS(DZ\$3:DZ\$2668,\$E\$3187,E\$3:E\$2668,\$D3187)/
COUNTIFS(\$E\$3:\$E\$2668,\$D3187,DZ\$3:DZ\$2668,"?*")
``````

Replies
4
Views
103
Replies
12
Views
533
Replies
4
Views
120
Replies
6
Views
262
Replies
11
Views
216

1,195,582
Messages
6,010,582
Members
441,557
Latest member
Jbest23

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