I have two worksheets one called ‘products’ and one called ‘totals’.
‘Products’ has the following column headings starting in the range A1 to A5: ‘Sales’, ‘apples’, ‘oranges’, ‘pears’, ‘bananas’. Cells B2 to B5 contain numeric data. I would like a formula in cell A2 that totals all cells in row 2 that have a heading (row 1) listed in the ‘totals’ sheet.
For example if the ‘totals’ sheet cell
A1 contains ‘Sales’ heading, A2 contains ‘apples’ and A3 contains ‘Pears’; then the formula in A2 would match it’s heading (‘sales’) to the corresponding column in the ‘totals’ sheet and then add across only the cells in row 2 on the ‘Products’ sheet which have headings matching those listed under ‘sales’ in the ‘totals’ sheet ie only total the numeric data for ‘apples’ and ‘pears’.
I want this formula to be as flexible as possible to handle as many headings as can be listed across row 1 on the ‘totals’ sheet with as many items to total listed underneath.
For example if on the ‘totals’ sheet I made another heading called ‘yellow fruit’ in cell B1 and put ‘bananas’ in cell B2, I could then on the ‘products’ sheet insert a column headed ‘yellow fruit’ next to the column headed ‘sales’, drag across the formula and it would know to only total ‘bananas’ data.
Hope that makes sense!
‘Products’ has the following column headings starting in the range A1 to A5: ‘Sales’, ‘apples’, ‘oranges’, ‘pears’, ‘bananas’. Cells B2 to B5 contain numeric data. I would like a formula in cell A2 that totals all cells in row 2 that have a heading (row 1) listed in the ‘totals’ sheet.
For example if the ‘totals’ sheet cell
A1 contains ‘Sales’ heading, A2 contains ‘apples’ and A3 contains ‘Pears’; then the formula in A2 would match it’s heading (‘sales’) to the corresponding column in the ‘totals’ sheet and then add across only the cells in row 2 on the ‘Products’ sheet which have headings matching those listed under ‘sales’ in the ‘totals’ sheet ie only total the numeric data for ‘apples’ and ‘pears’.
I want this formula to be as flexible as possible to handle as many headings as can be listed across row 1 on the ‘totals’ sheet with as many items to total listed underneath.
For example if on the ‘totals’ sheet I made another heading called ‘yellow fruit’ in cell B1 and put ‘bananas’ in cell B2, I could then on the ‘products’ sheet insert a column headed ‘yellow fruit’ next to the column headed ‘sales’, drag across the formula and it would know to only total ‘bananas’ data.
Hope that makes sense!
