Count IF function and matching criteria in different columns

[maggiec27]

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>

 

[Dallin]

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.

 

[maggiec27]

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]

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

 

[CheryBTL]

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

 

[maggiec27]

=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.

 

[Aladin Akyurek]

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

=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:

=COUNTIFS(DZ$3:DZ$2668,$E$3187,E$3:E$2668,$D3187)/
  COUNTIFS($E$3:$E$2668,$D3187,DZ$3:DZ$2668,"?*")