Sum without hidden rows and text

[schang_825]

Hi All,

I currently have a spreadsheet without both numbers and text in the cell. The text is required for conditional formatting. At the bottom of the columns, I wanted to sum only the numbers and also to exclude hidden rows. I currently have an array formula to sum the numbers, but can't seem to figure out how to exclude the hidden rows:

=SUM(IF(ISNUMBER(SEARCH("Pre-Con",E12:E66)),SUBSTITUTE(E12:E66,CHAR(10)&"Pre-Con","")+0))+SUM(IF(ISNUMBER(SEARCH("Const",E12:E66)),SUBSTITUTE(E12:E66,CHAR(10)&"Const","")+0))+ SUM(IF(ISNUMBER(SEARCH("Post-Con",E12:E66)),SUBSTITUTE(E12:E66,CHAR(10)&"Post-Con","")+0))

Any help would be greatly appreciated!!

Thanks!!

 

[jbeaucaire]

Conditional formatting of one cell can also work based on the values of adjacent cells. I would recommend putting your text in one column and numbers in the next, adjust your conditional formatting to color based on the content of the text in the adjacent cell as needed.

Then you can greatly simplify your formulas and employ the SUBTOTAL() function which, by design, will only total visible rows.

 

[schang_825]

Thank you for your reply. I didn't want to create extra rows for the text, but you're right. The formulas are much simpler once I split the text and numbers into different cells.