Count if <=(number) then count displayed Zeros displayed in cell, but ignore BLANK cells

[BroDad]

Hi all,

I need help on this formula...

=COUNTIF(F3:F12,"<=2")

it is counting everything less than or equals to 2, but it's also including the BLANK Cells within its range.


Coincidentally, this formula does the same thing...

=COUNTIFS(F3:F12,"<=2",F3:F12,"=0")

what do i need to add/omit/change so that it does not include the blank/null cells?

any help is greatly appreciated. ^_^

[Sam Capricci]

not sure why this =COUNTIF(F3:F12,"<=2") is counting blank cells, it doesn't for me.
maybe post a workbook with it?

[BroDad]

Okay. That's really odd. As i was building the workbook to upload here... IT DOES WORK! I will probably have to rebuild the Workbook. Thanks for confirming the formula Sam.

Quick Question: in Excel OPTIONS > ADVANCED > Display Options for Worksheet ... does a checked "Show a zero in cells that have zero value" affect my question?

[Fluff13]

Are you sure that the "blank" cells are actually empty, rather than containing 0 which is hidden in some way?

Edit just seen previous post

does a checked "Show a zero in cells that have zero value" affect my question?

Yes it does.

[BroDad]

The answer is "No" on if the cells are actually empty. The blank cells have a formula in it. It's calculating 2 cells against each other to get a person's age.

Just now dropping in that formula is counting the "blank (not blank cell)" as 1.

Is there a way around this?

[Fluff13]

What is the formula in those cells?

[BroDad]

=int(yearfrac(e3,am3))

[Fluff13]

That formula will leave 0s, one way round that is to use

Please Login or Register  to view this content.

[BroDad]

Yes, the Zeros needs to be counted but not Blank Cells.

Attachment 782789

Is there a way, that while the formula in calculating the age is present in the cell and the absence of both the DOB and Date, the running formula won't count Blank Cells but once the dates are provided, and if the dates don't amount to 1 year, it will consider a Zero as a count of 1?

I appreciate all the assistance i have received greatly!

[Fluff13]

Can you provide a sample workbook.

[BroDad]

How do i upload a workbook? i thought i saw an attachment button?

Attachment 782795

[BroDad]

Ugh. I have the workbook ready but i get this error...

"You are not allowed to post any kinds of links, images or videos until you post a few times."

[Fluff13]

To upload a workbook read the yellow banner at the top of the site entitled "HOW TO ATTACH YOUR SAMPLE WORKBOOK:"

[CARACALLA]

=sumproduct((f3:f12<=2)*(f3:f12<>""))

[BroDad]

Here you go...

lol. apologies for the images

[Fluff13]

Thanks for that. How about

Please Login or Register  to view this content.

[BroDad]

Thanks for that. How about

Please Login or Register  to view this content.

IT WORKED!

And it also ignores a blank value!

Attachment 782804

Repping where repping is due!

Thanks Fluff13 for this and Caracalla for chiming in (i did try your solution).

-Bro Dad

[Fluff13]

Glad to help & thanks for the feedback.