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

[Test_Database_2021]

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?

 

[arthurbr]

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]

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]

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