Variation on an IF formula

[tylerf]

Good afternoon,

I am in need of a formula that will sum a different set of cells baased on the value in another cell. below is an example.

Thanks in advance!

sample WS.xlsx

[nemo74]

How does this feel?

Please Login or Register  to view this content.

[Jonmo1]

There's got to be a better way..

Is there more information in your actual sheet? Another column that might indicate if each value in the column is related to Plan A or Plan B ?

Say if Column B = "Plan A" then Sum the corresponding value in Column A
You can then use SUMIF
=SUMIF(B:B,"Plan A",A:A)

[FDibbins]

This will work for you, based on your sample...

=IF(B22="PlanA",B4+B6+B10,B13+B14+B17)

However, if there is a specific code in A that you want to add together, you could probably use a sumif() function instead

[tylerf]

Jonmo,

Nemo74's formula works, and yes, I agree that your way would be much easier, however the problem is that some of the information might be applicable to more than one plan, so I don't know how I would get around that.

Thanks!

[FDibbins]

what "plan" names do you have??

[tylerf]

All I can say is the following without giving away confidential business information.

Plan A
Plan B
Plan C
Plan D
Plan E
Plan F
Plan G
Plan H
Plan I

[nemo74]

I think there are a million ways to do things in excel, the trick is just seeing all the data. With what he provided I can't think of another way. Prob is your IF formula would be long with all your codes.

[tylerf]

Absolutely. I actually think this will work, so I really appreciate all of the help.

Have a great weekend!

[DaveDeV]

An interesting thread....

Problem as stated was a specific instance lacking the rules for more general use and so - to an extent - was the solution put forward. I guess he'll be back...

Given the concerns of proprietary info, let's try to come up with a common domestic analogy:

For the OP's "Plan" let's use "Recipe"
For the OP's Column A let's use "Ingredients" (some of which could recur in multiple recipes)
The requirement is to sum only those Ingredients required in the selected Recipe.

If this analogy is valid, then there is some data missing from the info provided - namely, lists of which Ingredients comprise each Recipe.

The simplest way to structure this for data entry is probably via a 2-dimensional matrix where the Ingredients are listed across columns and the Recipes down rows. For each recipe, those ingredients required can be assigned a value of "Y" at the intersection cells.

NB: This can be expanded by dropping the "Y" in favour of a quantity required - thereby incorporating some "configuration rules"

For each "Recipe" construct a concatenated string of only those "Ingredient" codes with a "Y" (or a value >0, in which case, concatenate the quantity as well).

The use of concatenation of only active items provides a contiguous list capability as opposed to just totals by unpacking the string in a separate summary table.

I need to get to the bank before it closes so have to run, but when I get back, I'll knock together an example workbook and post it as an attachment.

PS: I used this approach many years ago (in Multiplan) when in IT product marketing to build computer system configurator tools which was very similar but needed to include dependency-type rules.

Until later...

Dave

[DaveDeV]

Back...

Okay, I've knocked together a sample workbook that comprises the following worksheets:

1) Tables and Lists for lookups and validation
2) Configuration table to allow Plans to be "configured" with components and to construct configuration string per plan
3) Evaluation sheet, which allows Plan to be selected, retrieves the string and unpacks it to build a cost table

This approach of "build compacted string then unpack" eliminates blank lines in the final evaluation

NOTE: This approach has been adopted to make understanding of the logic easier - NOT as a "plug 'n play module

I hope this helps...

Dave

ATTACHMENT:

ComponentsPerPlanEvaluation-DdV.xlsx