COUNTIFs is "undercounting" (not counting all the cells)

Test_Database_2021

New Member
Joined
Apr 6, 2021
Messages
9
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Hi!

So I have a table with multiple COUNTIFs formulas. The total Excel file is 4MB.
However, none of the COUNTIFs formulas reference other formulas, just text, so it should not be too complicated.

Even so, many of the formulas are "undercounting" (not counting all the cells).
E.g. this formula: =COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9,Patients!$Z:$Z,B15)

Things I have already tried:
- making sure the source cell range Z really does contain the exact same text as B15
- trying to replace the range Z with the concrete cells (Z1:Z4198) - but then it gives me #VALUE error
- copy/pasting absolutely everything into a new Excel file
- starting a new Excel file that is smaller and contains just a few formulas

--> What could be wrong?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,311
Office Version
  1. 2010
Have you checked if the text does not contain trailing spaces somewhere?
Perhaps add wildcards =COUNTIFS(Patients!$R:$R,"<>",Patients!$R:$R,"<>"&Metadata!$O$9&"*",Patients!$Z:$Z,B15&"*")
Do any cells in col R contain null strings ("") ? They are not counted as empty cells
 

Test_Database_2021

New Member
Joined
Apr 6, 2021
Messages
9
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Thank you!!!!!!!! That just fixed it! Though I am a bit confused why because I had done a search/replace on all the text in the entire table repeatedly to eliminate random spaces or individual bits of text that could be different.

Your formula works perfectly! Just for my understanding/learning: So to be sure, I should add wildcards in all my formulas?
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,311
Office Version
  1. 2010
So to be sure, I should add wildcards in all my formulas?
I think that is no problem, just use the correct wildcard. But wildcards are not available in all functions in Excel. Always check the proper syntax. There are usually workarounds when the wildcards do not work in a given function
 

Forum statistics

Threads
1,143,673
Messages
5,720,223
Members
422,270
Latest member
CaptainMurray

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
Top