IF formula to make different cells total

**AlmostAGeek** · 02-04-2009, 10:24 PM

I am trying to create a formula for summing certain cells - but only if certain conditions are met. I have attached the worksheet to better help explain what I am trying to accomplish.

Basically:
If you achieved budgeted occupancy for this month you choose yes (Q20) and $500 appears in cell S21.

If you exceed budgeted occupancy you would fill in that number in cell P28 and your commission would fill in cell S29.

The problem arrives when I need to sum in cell V57. It needs to include either cells S21 or S29 but not both.

The sum in V57 should be either a total of S21 and V51 OR S26 and V51 OR S28 and V51.

Any help would be much appreciated - thanks in advance.

D

**DonkeyOte** · 02-05-2009, 05:05 AM

Originally Posted by AlmostAGeek

The sum in V57 should be either a total of S21 and V51 OR S26 and V51 OR S28 and V51.

I think you need to elaborate on the above such that we can follow the logic... ie what is/are the rules to determine which of S21,S26,S28 are to be added to V51 ?

On an aside... you can dispose of your lengthy double IF in S28 by using a LOOKUP, eg instead of:

Please Login or Register  to view this content.

use:

Please Login or Register  to view this content.

If you post back we can wrap this up quite quickly I suspect.

**arthurbr** · 02-05-2009, 05:18 AM

I also noted that you use merged cells a lot. Be aware that aside form looking nice they can cause lots of trouble in formulas.
Better get rid of them and format Horizontal as " center across selection".

The sum in V57 should be either a total of S21 and V51 OR S26 and V51 OR S28 and V51.

Did you not mean S29 instead of S28?
Can there be values in two of the SXX cells at the same time ?

**AlmostAGeek** · 02-05-2009, 06:54 PM

I want to thank both of you for your suggestions.

Since they were pressed for time it was decided that we were going to adjust the formula in S29 and reduce the dollar amounts by $500 each so the total would just come out right.

In the mean time I want to take another look at this a do it the right way.

And Arthur, you are correct, it should have been S29. No, only one SXX and V51 per total. We pay either S21 or S26 or S29 and V51. I will also work on getting rid of alot of the merged cells. What is the major difference in how Excel looks at those as opposed to center across selection?

Thanks again, the time you spend is appreciated.

D