Help with formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1755112%22%20slang%3D%22en-US%22%3EHelp%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1755112%22%20slang%3D%22en-US%22%3EI%20am%20trying%20to%20count%20the%20number%20of%20entities%20that%20have%20paid%20%E2%80%9C%240.00%E2%80%9D%20towards%20a%20balance%20they%20have%20with%20us.%20I%E2%80%99ve%20tried%20the%20counta%20and%20the%20countif%20formulas%20putting%20%3D0.00%20in%20the%20quotes%20but%20I%20have%20been%20getting%200%20as%20the%20answer.%20Does%20it%20matter%20that%20the%200.00%20is%20the%20result%20of%20a%20formula%20and%2For%20some%20cells%20in%20the%20column%20are%20blank%3F%3CBR%20%2F%3EPlease%20help.%20Thanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1755112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1755200%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1755200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823868%22%20target%3D%22_blank%22%3E%40Lenneahs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBlank%20cells%20don't%20matter%2C%20but%20the%20formulas%20might%20not%20return%20exactly%200.%3C%2FP%3E%0A%3CP%3ETry%20replacing%20formulas%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%3CEM%3Eexpression%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DROUND(%3CEM%3Eexpression%3C%2FEM%3E%2C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20that%20solve%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1755429%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1755429%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20response.%20I%20understand%20what%20you%20are%20saying%20and%20that%20could%20help.%20Basically%2C%20I%20am%20trying%20to%20get%20a%20formula%20that%20will%20count%20all%20who%20have%20%240.00%20in%20column%20I-%20%E2%80%9Cpartial%20amount%20paid%20to%20date%E2%80%9D%20in%20the%20attachment.%20It%20should%20be%2012%20on%20the%209%2F30%20worksheet%20and%2041%20on%20the%208%2F30%20worksheet%20if%20you%20count%20them%20up%20the%20hard%20way.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1755802%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1755802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823868%22%20target%3D%22_blank%22%3E%40Lenneahs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(I2%3AI199%2C%22%26lt%3B0.01%22%2CI2%3AI199%2C%22%26gt%3B-0.01%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
I am trying to count the number of entities that have paid “$0.00” towards a balance they have with us. I’ve tried the counta and the countif formulas putting =0.00 in the quotes but I have been getting 0 as the answer. Does it matter that the 0.00 is the result of a formula and/or some cells in the column are blank?
Please help. Thanks.
4 Replies

@Lenneahs 

Blank cells don't matter, but the formulas might not return exactly 0.

Try replacing formulas

 

=expression

 

with

 

=ROUND(expression,2)

 

Does that solve the problem?

Thanks for your response. I understand what you are saying and that could help. Basically, I am trying to get a formula that will count all who have $0.00 in column I- “partial amount paid to date” in the attachment. It should be 12 on the 9/30 worksheet and 41 on the 8/30 worksheet if you count them up the hard way.

@Lenneahs 

Use

=COUNTIFS(I2:I199,"<0.01",I2:I199,">-0.01")

Thank you so much! It worked!!