Count text cells if condition in row is true, over many rows.
Using Excel 2003 - 84 columns, up to 250 rows. By groups of 3 adjacent
columns, I want to put (in merged cell at top of each group of 3) a formula showing total of cells with text, IF a separate cell in column CM in each row has a particular value. So, there will be 28 totals (84/3) in top row. If CM2="E", CM3="S", CM4="E", CM5="E", CM6="S", etc, and using condition "E", the formula in merged D1 will count all text cells in columns D,E,F of rows 2,4,5,etc. The formula in merged G1 will count all text cells in columns G,H,I of rows 2,4,5,etc. And so on for an additional 26 totals over the next 78 columns. Similarly, using condition "S", the formula in merged D1 would count all text cells in columns D,E,F of rows 3,6,etc. The formula in merged G1 would count all text cells in column G,H,I of rows 3,6,etc. And so on for an additional 26 totals over the next 78 columns. With conditional formatting, the text in all "E" rows is color black, the text in all "S" rows is color red, if a formula could use both the presence of text in each cell and its color to decide whether to include it in the total. If not possible in Excel 2003, is this something that would be possible in Excel 2007 (which I haven't used) due to additional formula capabilities? I want to use formulas because the distribution of text in the 84x250 area can vary. |
All times are GMT +1. The time now is 09:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com