COUNTIF based on two columns with one containing merged cells

[smms1]

I have two columns with one column having multiple merged cells and another with mixed of blank and non-blank cells.

I tried =COUNTIFS(D3:D14,"Red",E3:E14,"<>") but did not succeed. Any suggestion pls (merged cells are unavoidable)?

Here is the excel file Upload Files - Dropfiles.org

Thanks!

 

[jasonb75]

Please provide your sample layout using XL2BB (link in my signature below). Many people are not able to download files from share sites due to security restrictions.

With merged cells, (as an example) if you have E3:F4 merged, only E3 (the top left cell of the block that is merged) has a value, the other cells are all evaluated as empty. This prevents most formulas from working correctly.

 

[Tom.Jones]

If in column E, those data are NOT the result of formulas, and the range in columns D and E have the same size, then it is easier to use the COUNTA function in column E.
=COUNTA(E3:E14),
=COUNTA(E15:E19)

It is good to avoid merged cells.