The function isblank returns True even though the range contains nonempty cells

Question

I have a number of log sheets then a single sheet used to pool data from each individual log.

Example Log Sheet:

Data Sheet Example:

The data sheet counts the number of times the correct color is picked against the roll, then sorts it by room.

Formula for that is:

 =IF(OR(ISBLANK(Sheet1!$B:$B),ISBLANK(Sheet1!$C:$C)),0,SUMPRODUCT(Sheet1!$A:$A=A2, Sheet1!$B:$B=Sheet1!$C:$C))

With Sheet1 changed to Sheet2 and Sheet3 for their respective tables.

The problem is the OR check will only correctly return true/false if the targeted log sheet has data on the same row that the count formula is on.

In the above example, each log sheet is identical with 10 rows of data including the header, then on the data sheet the OR check stops working at B11.

If data is added to row11 on Sheet2 then the OR check will work and the sumproduct will correctly count data on all rows including rows 1:10.

Then again on B12 of the data sheet, it requires data on row12 on the log sheet to work.

Link to Example Sheet

I don't know how to get around it. Happens even if I manually enter the specific row numbers. Anyone have any ideas or know a way around it?

Answer 1

The isblank function is intended for use with an individual cell; its behavior when applied to a range is not documented as far as I know. In your case, isblank($B:$B) returns True if it's placed in a row where B column is empty.

Use counta(...)>0 instead, like this:

=IF(COUNTA(Sheet1!$B:$B)*COUNTA(Sheet1!$C:$C)=0, 0, SUMPRODUCT(Sheet1!$A:$A=F2, Sheet1!$B:$B=Sheet1!$C:$C))