count visible merged cells in Excel 2010

0
votes

I have Excel sheet as below with merged cells in column A

excel sheet

in Cell D1 SUBTOTAL works fine

But when i filter Column b as below

Filtered sheet

SUBTOTAL not count MERGED cells, it only count row 22.

I tried use Countif but it count all rows(visible and hidden)

How i can count all visible rows on column A?

1
excelexcel-formulaexcel-2010
sorry, not the same, my problem on MERGED cells and i already use SUBTOTAL but not worked with merged cells. – E R
Stop using Merged Cells. Anyway they produce a lot of other issues. – Patrick Honorez

1 Answers

0
votes

I would use the following workaround: in cell C2 use formula =INT(SUBTOTAL(3, B2:B4)>0) and similar formulas in cells C5, C9, C13, C16, C19, C22.. just replace B2:B4 with the respective range of rows of the merged cells. The formula will tell you whether at least one of the rows in a group is visible. Then make SUM of all values in the column C.