CountIf blank with two columns

Hi,

I need help counting cells if blank. I have two columns E and J . For example if E5 and J5 are both blank I want that to count as 1.


Appreciate the help.

Gibs

This should work

Please Login or Register  to view this content.

If I understand you right, I think you need something like this

Please Login or Register  to view this content.

Originally Posted by whallgren

If I understand you right, I think you need something like this

Please Login or Register  to view this content.

Thanks for the reply. But this formula does not work correctly.


Column E total not blank = 2308
Column J total not blank = 1911

Your formula gives a total not blank = 2605

The total has to be below 1911. As the not blank fields will not match exactly. One or the other column could be blank, therefore giving a lesser value than the lowest I have.

Thanks,

Gibs

Originally Posted by Palmetto

Using a helper column, put this formula into the cell

=AND(LEN(E5)=0,LEN(J5)=0)

This will return TRUE or FALSE
Count the TRUE values.

Hi Thanks for the quick reply.

I tried your formula but it only returns "False" no count.

Thanks,

Gibs

Originally Posted by quekbc

This should work

Please Login or Register  to view this content.

Hi,

Thanks for the fast reply. I tried your formula but it appears to only check row 5 ?

I need to check from row 2 to 5,000.

Anyway it returns the value 0 (zero). Cells E5 and J5 are not blank. It should at least return 1 I would think. But the formula does not work.

Thanks,

Gibs

Originally Posted by Palmetto

The formula I gave you will return TRUE only if both cells are in fact blank, which includes no invisible space characters.

Perhaps you should provide a more clear explanation of your requirements. Do both cells on the same row need to be blank or just any cells in the columns.

Hi,

I want to count rows 2 to 5,000. Columns E and J.

I gave an example. If E2 and J2 are both blank I want that to count as 1.

If E9 is blank and J9 is not blank, count as 0.

Column E and J must not be blank on the same row to count as 1. I need a total.


Gibs

Originally Posted by NBVC

Your information is contradicting..one minute you say count blanks, the next minute you say count non-blanks... you have done that more than once in this thread... wallgreen's formula counts when both are blank... change the

=SUMPRODUCT(--(E2:E5000=""),--(J2:J5000="")) this counts blanks

=SUMPRODUCT(--(E2:E5000<>""),--(J2:J5000<>"")) this counts non-blanks

Yeah thanks for pointing that out in an assinine way. That's because I have to explain over and over with three formulas that dont work! READ THE TITLE COUNTIF BLANK!!!

Gibs

Originally Posted by NBVC

Your information is contradicting..one minute you say count blanks, the next minute you say count non-blanks... you have done that more than once in this thread... wallgreen's formula counts when both are blank... change the

=SUMPRODUCT(--(E2:E5000=""),--(J2:J5000="")) this counts blanks

=SUMPRODUCT(--(E2:E5000<>""),--(J2:J5000<>"")) this counts non-blanks

Yes this one --> =SUMPRODUCT(--(E2:E5000<>""),--(J2:J5000<>"")) seems to work.

Thank you,

Gibs

The more i read this the more confused i am.

Both formula should work.

It's more of a question of the result being the result that you actually want (i.e. count blanks or count non-blanks).