Multiple Scenarios

[Steve Hickman]

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?

 

[Shane Devenshire]

Hi,

The basic idea would be a SUMPRODUCT or DSUM function in 2003 or earlier and
those along with a SUMIFS or COUNTIFS in 2007. However, without more details
all I can do is provide an example:

These are all summing examples:

=SUMPRODUCT(--(A$2:A$16>=50),--(B$2:B$16="Cherries"),A2:A16)
=SUMIFS(A2:A16,A2:A16,D2,B2:B16,E2)
=DSUM(A1:B16,A1,D1:E2)

The DSUM example requires a criteria range. In the first example the
criteria are hard coded into the formula in the last they are in ranges in
the spreadsheet. The first two can do either, the third requires the range.

 

[Steve Hickman]

Sorry, have tried the various solutions (initial query was bounced back as
error msg came up, so reposted and they both ended up on the boards) with no
success.

What I am really trying to do is some form of lookup so that if the fruit is
included to be aggregated (simple Yes/No list) in a particular week, a total
will be derived across the categories, e.g. below would mean that 750 would
be totalled for Apples and Pears in week of 1 Jan. I've succeeded by doing
=sumif(B3="Yes",b4)+sumif(d3="Yes",d4)=sumif(f3="Yes",f4) but I wondered if
there was an easier way?

Apples Pears Oranges
01-Jan 08-Jan 01-Jan 08-Jan 01-Jan 08-Jan
Included? Yes Yes Yes Yes No No
Sales 400 450 300 275 10 15
Costs (240) (270) (180) (165) (7) (11)
Profit 160 180 120 110 3 4