COUNTIF not working

[sjha]

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)

Can you please help me what I am doing wrong here?

Thank you.

 

[Lewiy]

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

 

[NBVC]

Check your parenthesis....

try:

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

 

[sjha]

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]

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]

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]

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]

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]

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]

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.