# COUNTIF not working

#### sjha

##### Active Member
Good day to all,

I am using the following formula to count the total number of contract types if 'ITD \$K' equals '0' zero. But it returns 0 as output.

=SUMPRODUCT('PROCUREMENT CONTRACTS'!\$E\$5:\$E\$225="GROSS")*('PROCUREMENT CONTRACTS'!\$J\$5:\$J\$225=0)

Thank you.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Lewiy

##### Well-known Member
Code:
``=SUMPRODUCT(('PROCUREMENT CONTRACTS'!\$E\$5:\$E\$225="GROSS")*('PROCUREMENT CONTRACTS'!\$J\$5:\$J\$225=0))``

#### NBVC

##### Well-known Member

try:

=SUMPRODUCT(('PROCUREMENT CONTRACTS'!\$E\$5:\$E\$225="GROSS")*('PROCUREMENT CONTRACTS'!\$J\$5:\$J\$225=0))

#### sjha

##### Active Member
Thank you for the reply. It is working but pulling the wrong output and the reason may be is the formatting. I have second criteria column is formatted as Number with 2 decimal places. So, 0 shows as 0.00, and other numbers are as follows

0.00
0.05
0.08
0.18
0.25
0.34

You think 0.05, 0.08 are counting as 0? If so, how can I re-write the formula? I appreciate it. Thank you.

#### Lewiy

##### Well-known Member
It will not matter what the number format is, if the cell value is truly zero then it will count it, if it is not truly zero then it will not count it.

#### sjha

##### Active Member
Thank you Lewiy,

With respect, I think this does matter since if I format the cells with 0 decimal, Excels looks all the cell values less then 0.50 as 0 and there is the problem. I want the formula to look exactly 0.00 since my column is formatted to 2 decimals. I hope I explained it clearly. Thank you for looking into it.

#### Lewiy

##### Well-known Member
Even if you formatted your cell to no decimal places, if you entered the value 0.423 although it would appear as 0, Excel would treat it as 0.423 when it comes to calculations/formulas. Formatting has no effect on the value of a cell, just how it is displayed.

#### sjha

##### Active Member
Thank you...so, what is the reason is it not pulling the exact counts? Some how excel formula is counting all the cells that has values less then '0.50' as value 0. I am so confused. Thank you.

#### sjha

##### Active Member
Thank you...so, what is the reason is it not pulling the exact counts? Some how excel formula is counting all the cells that has values less then '0.50' as value 0. I am so confused. Thank you.

I have also tried,

=SUMPRODUCT(('PROCUREMENT CONTRACTS'!\$E\$5:\$E\$196="GROSS")*('PROCUREMENT CONTRACTS'!\$J\$5:\$J\$196<0.01))

but it is pulling the same.

#### sjha

##### Active Member
Thank you...so, what is the reason is it not pulling the exact counts? Some how excel formula is counting all the cells that has values less then '0.50' as value 0. I am so confused. Thank you.

I have also tried,

=SUMPRODUCT(('PROCUREMENT CONTRACTS'!\$E\$5:\$E\$196="GROSS")*('PROCUREMENT CONTRACTS'!\$J\$5:\$J\$196<0.01))

but it is pulling the same.

Replies
5
Views
105
Replies
17
Views
360
Replies
4
Views
195
Replies
9
Views
369
Replies
3
Views
158

1,191,007
Messages
5,984,129
Members
439,872
Latest member
ExcelRM

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