When is a blank cell not a blank cell?

[gizzard]

I'm using SUMPRODUCT to tally the results based on a few conditions I've set. One of the conditions is the number of days between two dates. To count the days, I'm using DAYS360 and an IF statement to check if the cell is empty.

My problem is that SUMPRODUCT sees an "empty" cell as containing a number that is greater than 0. Of course, this is affecting my tally. I'm puzzled as to what I am doing wrong and how Excel "sees" blank cells.

I've attached a sample sheet for reference. I'd appreciate any tips and advice!

[bentleybob]

Try adding

Please Login or Register  to view this content.

in your SUMPRODUCT formula.

[daddylonglegs]

DAYS360 function shouldn't normally be used to get an accurate difference in days between 2 dates. As the name implies it calculates as if there are 360 days in a year (30 in each month) and it should probably only be used if you have an accounting system which works on a 360 day year basis.

To calculate the difference in days between 2 dates you can simply subtract the smaller (older) date from the other one. Based on that you can use this formula in C7 copied down

=IF(B7="","",B$1-B7)

Note that using that formula you get 180 in C12 rather than 179, which I would suggest is the correct result.

For A4 try COUNTIFS which will not see a blank cell as greater than 30, i.e.

=COUNTIFS($A$7:$A$1000,"hiring",$C$7:$C$1000,">=30")

[Jack in the UK]

I would use LEN and test the cell for easter eggs, than bold the SUMPRODUCT via an IF formula

Another way maybe.. ie test the cell and act as findings

[gizzard]

daddylonglegs, thank you for the suggestion about the dates - such a simple solution that I wasn't aware of!

I had been using COUNTIFS, but converted to SUMPRODUCT since this spreadsheet needs to exist on EditGrid, an online version of Excel, but with some limitations. Lacking COUNTIFS formula was one of those limitations. =)

[gizzard]

bentleybob, I added that condition to the sample spreadsheet and it worked! Thank you! I'll try to apply it to my spreadsheet.

Does that condition evaluate to TRUE only if there is a value greater or less than "nothing"?

[daddylonglegs]

With Bob's suggestion, i.e.

$C$7:$C$1000<>""

you get FALSE only if the cell is either (a) completely blank or (b) contains a "formula blank", i.e. "" returned by a formula.

The problem, essentially, is that any text value is deemed to be greater than any number....so "" is a text value and will therefore always be > 30....so you need the extra condition to "filter out" the rows with formula blanks. Another way would be to check that the cells contain numbers, i.e.

ISNUMBER($C$7:$C$1000)

[gizzard]

Thank you for the explanation. I've learned so much from this community in such a short time.