I am trying to set up a formula to allow five variables to be aggregated (or
not)when they are switched on or off.

e.g.

Apples Pears Oranges Peaches Strawberries
Yes Yes No Yes No
Sales 500 330 200 199 54
Costs -30 -44 -180 -99 -30
Profit 470 286 20 100 24

I want to set the formula up so that Apples, Pears and Peaches would be
aggregated in the above example. I can't seem to do this through IF as there
are too many outcomes. Similarly, SUMIF works on the above, however I have
weekly data for each of the above fruit and SUMIF doesn't work if I put time
intervals in.

This seems to be a pretty basic modelling query and I'm reasonably
experienced, but it has got me totally stumped! Any ideas?

Hi,

You really shouldn't post twice, however, I will respond twice differently.
Why not try a pivot table? Its very flexible and you will be able to aggrate
as you need and filter based on a time period.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Steve Hickman" wrote:

I am trying to set up a formula to allow five variables to be aggregated (or
not)when they are switched on or off.

e.g.

Apples Pears Oranges Peaches Strawberries
Yes Yes No Yes No
Sales 500 330 200 199 54
Costs -30 -44 -180 -99 -30
Profit 470 286 20 100 24

I want to set the formula up so that Apples, Pears and Peaches would be
aggregated in the above example. I can't seem to do this through IF as there
are too many outcomes. Similarly, SUMIF works on the above, however I have
weekly data for each of the above fruit and SUMIF doesn't work if I put time
intervals in.

This seems to be a pretty basic modelling query and I'm reasonably
experienced, but it has got me totally stumped! Any ideas?