# COUNTIFS function is ignoring Empty cells, How to match criteria which include empty cells ??

#### govind_bhuse

##### New Member
Dear All,

I am trying to create a COUNTIFS formula which can fetch me the below desired result.
In below example one of the source cell is empty but I want countifs formula to consider this as a count.

 Name Middle Name Surname Count Formulas I Want Answer Omkar Govind Bhuse 1 =COUNTIFS(\$B\$3:\$B\$13,B3,\$C\$3:\$C\$13,C3,\$D\$3:\$D\$13,D3) Manasvi Govind Bhuse 1 =COUNTIFS(\$B\$3:\$B\$13,B4,\$C\$3:\$C\$13,C4,\$D\$3:\$D\$13,D4) Swaroop Indrajit 0 =COUNTIFS(\$B\$3:\$B\$13,B5,\$C\$3:\$C\$13,C5,\$D\$3:\$D\$13,D5) 2 Swaroop Indrajit 0 =COUNTIFS(\$B\$3:\$B\$13,B6,\$C\$3:\$C\$13,C6,\$D\$3:\$D\$13,D6) 2 Rupali Govind Bhuse 3 =COUNTIFS(\$B\$3:\$B\$13,B7,\$C\$3:\$C\$13,C7,\$D\$3:\$D\$13,D7) Rupali Govind Bhuse 3 =COUNTIFS(\$B\$3:\$B\$13,B8,\$C\$3:\$C\$13,C8,\$D\$3:\$D\$13,D8) Rupali Govind Bhuse 3 =COUNTIFS(\$B\$3:\$B\$13,B9,\$C\$3:\$C\$13,C9,\$D\$3:\$D\$13,D9) Indrajit Bhuse 0 =COUNTIFS(\$B\$3:\$B\$13,B10,\$C\$3:\$C\$13,C10,\$D\$3:\$D\$13,D10) 2 Indrajit Bhuse 0 =COUNTIFS(\$B\$3:\$B\$13,B11,\$C\$3:\$C\$13,C11,\$D\$3:\$D\$13,D11) 2 Bhagyashri Indrajit Bhuse 2 =COUNTIFS(\$B\$3:\$B\$13,B12,\$C\$3:\$C\$13,C12,\$D\$3:\$D\$13,D12) Bhagyashri Indrajit Bhuse 2 =COUNTIFS(\$B\$3:\$B\$13,B13,\$C\$3:\$C\$13,C13,\$D\$3:\$D\$13,D13)

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### AliGW

##### Banned

=SUMPRODUCT((\$B\$3:\$B\$13=B3)*(\$C\$3:\$C\$13=C3)*(\$D\$3:\$D\$13=A3))

Thanks AliGW,

#### AliGW

##### Banned
You are very welcome!

There was a typo, of course, and I should have typed this:

=SUMPRODUCT((\$B\$3:\$B\$13=B3)*(\$C\$3:\$C\$13=C3)*(\$D\$3:\$D\$13=D3))

Last edited:

Replies
6
Views
104
Replies
4
Views
112
Replies
2
Views
311
Replies
3
Views
91
Replies
4
Views
234

1,195,849
Messages
6,011,955
Members
441,657
Latest member
Diupsy

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

### Which adblocker are you using?

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

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