Forum Discussion
anthwny
Mar 08, 2024Copper Contributor
COUNTIFS Non-Empty Cells
Hi! I'm new to excel and want to use COUNTIFS, but I don't want it to count empty cells.
My formula is like this:
=COUNTIFS($C$4:$C$10001;H4;$B$4:$B$10001;G4)
And it works fine when everything has values, but I don't want it to count empty cells, what should I add?
- binodranabhatCopper Contributor
anthwny I did not understand what do you mean by counting empty cells.
B C G H Result
AAA BBB AAA BBB 2 AAA BBB 0
=COUNTIFS($C$5:$C$10002,H4,$B$5:$B$10002,G4)
When you use the formula it will evaluate both expression as AND function ie
AND( ($C$5:$C$10002,H4), ($B$5:$B$10002,G4))
returning TRUE , TRUE then only the value starts adding up.
also it does not count the empty cells that- anthwnyCopper Contributor
binodranabhat Hi! Please see attached, you see when the data is empy, it adds upp all the empty cells
- binodranabhatCopper Contributor
- visolanki10Copper ContributorTo count non-empty cells using the COUNTIFS function in Excel, you can add additional criteria to check if the cells are not empty. You can use the "<>" operator to specify "not equal to" empty or blank cells.
=COUNTIFS($C$4:$C$10001; H4; $C$4:$C$10001; "<>"; $B$4:$B$10001; G4; $B$4:$B$10001; "<>")- anthwnyCopper Contributor
visolanki10 Hi! Thank you! I have tried to paste the formula you wrote, but I still get the same result, it still counts the empty ones. Should I add something else or could it be a locale-issue?
- beerExcelguy207Copper Contributor
In order to find the count of empty cells in a CountIfs formula, the criteria should also include &""
I.e. =COUNTIFS([criteria range1],"<>"&"",[criteria range2].....)