How to create formula including three different possible base numbers and two possible sum

[Diaxus]

This will be tricky to explain but hopefully easy to answer. I have three cells each requiring a "Y" to activate by =IF function; only one of these cells will activate at a time, and so the formula must function as a three-way switch, determining which base number to use; if none of these are active, the formula must read "5". After this is done, there are two cells that give a 1 or 0, also based on =IF functions. These two cells must be summed together with whichever base is selected from the previous switch function.

I have achieved the switch by way of a series of =IF functions which read as: =IF(Meridian!J17="Y",6,IF(Osiris!J5="Y",6,IF(Osiris!J6="Y",4,5)))

The =SUM formula is too easy to bother posting.

What I need is a way to combine these around the parameters specified above. Any help would be greatly appreciated.

[FDibbins]

Hi, welcome to the forum

You are a little vague about what the "base" will be, and is there any relation between the 3 bases?

See if this will get you started?

	A	B	C	D
1		Base1	Base2	Base3
2		10	20	30
3	10	y

A3=IFERROR(INDEX($B$2:$D$2,MATCH("y",B3:D3,0)),5)

If that doesnt help, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Beamernsw]

That is a little confusing Diaxus. I can't see how your existing formula doesn't do exactly what you asked......except to add the other 2 cells in.
If the other two "1 or 0" cells were A1 and B1 then:-

Please Login or Register  to view this content.

[Diaxus]

@FDibbins,
Well, the base will be one of three possibilities, and Beamernsw is correct in saying that my =IF formula does function as the switch between them. Each possibility is really a different source, and these sources amount to the numerical values 6, 6, or 4. While two of these are 6, they come from separate sources and so must be accounted separately.

Essentially, what I want is a formula that can operate as switch between these possible bases, and then also add the two additional sums if applicable, all in one formula. You could say this is an exercise in learning the intricacies of Excel.

I will look into your proposed formula, and get back to you on how it works.

Thank you for the warm welcome, and for your efforts in helping me

[Diaxus]

That is a little confusing Diaxus. I can't see how your existing formula doesn't do exactly what you asked......except to add the other 2 cells in.
If the other two "1 or 0" cells were A1 and B1 then:-

Please Login or Register  to view this content.

Can it really be so simple? I tried incorporating =SUM every way I could think of. I simply wasn't aware that anything could be added to the formula after all parenthesis. I think my question betrays my ignorance.

Thanks for your help