=COUNTIFS Question

D

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Am using this formula:

=COUNTIFS('Invalid Results'!$AV$5:$AV$1004,$B268,'Invalid Results'!$M$5:$M$1004,"Invalid RESULT")

And it works just fine unless the value in cell B268 is "< Not Applicable >".
Formula always returns zero even though I KNOW AV5:AV1004 cotains cells with "< Not Applicable >".

Anyone have any ideas what's going on here?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
I believe that COUNTIF(S) and SUMIF(S) would evaluate that not as determining the if the literal string "< Not Applicable >", but rather as less than "Not Applicable >", since the argument is actually a string argument.

Instead, try explicitly declaring the equal-to part:

=COUNTIFS('Invalid Results'!$AV$5:$AV$1004,"="&$B268,'Invalid Results'!$M$5:$M$1004,"Invalid RESULT")
 
Upvote 0
Is it exactly "< Not Applicable >" or maybe "<Not Applicable>" and so on.
 
Upvote 0
doofusboy said:
Am using this formula:

=COUNTIFS('Invalid Results'!$AV$5:$AV$1004,$B268,'Invalid Results'!$M$5:$M$1004,"Invalid RESULT")

And it works just fine unless the value in cell B268 is "< Not Applicable >".
Formula always returns zero even though I KNOW AV5:AV1004 cotains cells with "< Not Applicable >".

Anyone have any ideas what's going on here?
Click to expand...
Maybe this...

=COUNTIFS('Invalid Results'!$AV$5:$AV$1004,"*"&$B268&"*",'Invalid Results'!$M$5:$M$1004,"Invalid RESULT")

There might be some unseen whitespace characters in $AV$5:$AV$1004. Using wildcards should handle that but it only "masks" the problem.
 
Upvote 0
To illustrate my solution better:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BACKGROUND-COLOR: #ffff00">< 3</TD><TD>< 3</TD><TD>Without explicitly declaring =</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BACKGROUND-COLOR: #ffff00">< 3</TD><TD style="TEXT-ALIGN: right"></TD><TD>With explicitly declaring =</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BACKGROUND-COLOR: #ffff00">< 3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b050">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BACKGROUND-COLOR: #ffff00">< 3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D1</TH><TD style="TEXT-ALIGN: left">=COUNTIF(A:A,B1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=COUNTIF(A:A,"="&B1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
Figured it had to be the "<" character but wasn,'t sure what syntax to use to get around it.

Mr.Kowz nailed it with
explicitly declaring the equal-to part:
Click to expand...

Added the "="& and it worked like a charm.

Thanks everyone for their efforts.
 
Upvote 0
doofusboy said:
Figured it had to be the "<" character but wasn,'t sure what syntax to use to get around it.

Mr.Kowz nailed it with

Added the "="& and it worked like a charm.

Thanks everyone for their efforts.
Click to expand...
Glad you got it straightened out.

Just by dumb luck the suggestion I made will also work.

However, I misunderstood and thought the cell entry was simply "Not Applicable". I didn't know the brackets were part of the string.

On a side note, if you ever get stuck with something like this again you can always use the SUMPRODUCT function which uses literal comparisons.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Help please - COUNTA error
Replies
1
Views
137
StephenCrump
S
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
358
Kaper
Kaper
C
  • Question Question
Filter Formula and Match Formula - Not quite right
Replies
1
Views
247
StephenCrump
S
N
  • Question Question
Vlookup with max formula
Replies
1
Views
368
Fluff
Fluff
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
535
awoohaw
awoohaw

Forum statistics

Threads
1,224,583
Messages
6,179,673
Members
452,937
Latest member
Bhg1984

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top