COUNTIF not working

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Code:
=SUMPRODUCT(('PROCUREMENT CONTRACTS'!$E$5:$E$225="GROSS")*('PROCUREMENT CONTRACTS'!$J$5:$J$225=0))
 
Upvote 0
Check your parenthesis....

try:

=SUMPRODUCT(('PROCUREMENT CONTRACTS'!$E$5:$E$225="GROSS")*('PROCUREMENT CONTRACTS'!$J$5:$J$225=0))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top