CODEWITHSUNDEEP
excelfunctionif-statementsum
i431ad
i431ad

Reputation: 21

How do I calculate a Sum based on multiple If's in Excel?

Background is that I'm making a budget spreadsheet. I have different bills due on different days. (ie. bill due on Monday and bill due on the 10th)

I want a function that will place the appropriate amount of money going in/out in column D and the description of why the money is going in/out in column E.

Currently I have two different formulas that I created (probably incorrectly).

Formula for Column E: (Already is in the document and seems to work fine other than that fact that I cant add additional text to the cell)

=IF(DAY(C36)=7," Amy Pay","")&IF(DAY(C36)=22," Amy Pay","")&IF(DAY(C36)=8," Family Bills","")&IF(DAY(C36)=6," Dollar Shave Club","")&IF(DAY(C36)=2," Amy Cap One VISA","")&IF(DAY(C36)=3," Chase VISA","")&IF(DAY(C36)=8," Being Smart","")&IF(DAY(C36)=17," Gym","")&IF(DAY(C36)=11," Netflix","")&IF(DAY(C36)=19," Cap One MC","")&IF(DAY(C36)=29," CenturyLink","")&IF(DAY(C36)=6," Haley Cap One Visa","")&IF(DAY(C36)=10," SRP","")&IF(DAY(C36)=23, "Car Payment","")&IF(DAY(C36)=30, "Rent","")&IF((B36)="Mon"," Monday","")&IF((B36)="Fri"," Friday","")&IF((B36)="Fri"," Haley Pay","")

Formula for Column D: (not in the column yet, as it doesn't work how I want)

=IF(DAY(B40)=7,"1474.22","")&IF(DAY(B40)=22,"1474.22","")&IF(DAY(B40)=8,"-100","")&IF(DAY(B40)=6,"-9","")&IF(DAY(B40)=2,"-100","")&IF(DAY(B40)=3,"-100","")&IF(DAY(B40)=8,"-400","")&IF(DAY(B40)=17,"-20.05","")&IF(DAY(B40)=11,"-8.63","")&IF(DAY(B40)=19,"-450","")&IF(DAY(B40)=29,"-50","")&IF(DAY(B40)=6,"-150","")&IF(DAY(B40)=10,"-200","")&IF(DAY(B40)=23,"-325","")&IF(DAY(B40)=30,"-500","")&IF((A40)="Mon","-125","")&IF((A40)="Fri","-325","")&IF((A40)="Fri","400","")

http://imgur.com/IBINweh

      enter image description here

The problem is that in column D, rather than providing a sum of the numbers, it lists the numbers in the column.

http://imgur.com/rPDS5h2

      enter image description here

I had a suggestion to add =SUM( in front of the IF( function, but when I do, #VALUE! is what results in the field. Using this formula: (view image by changing appended text to /CVs0f1v ) 

=SUM(IF(DAY(B40)=7,"1474.22","")&IF(DAY(B40)=22,"1474.22","")&IF(DAY(B40)=8,"-100","")&IF(DAY(B40)=6,"-9","")&IF(DAY(B40)=2,"-100","")&IF(DAY(B40)=3,"-100","")&IF(DAY(B40)=8,"-400","")&IF(DAY(B40)=17,"-20.05","")&IF(DAY(B40)=11,"-8.63","")&IF(DAY(B40)=19,"-450","")&IF(DAY(B40)=29,"-50","")&IF(DAY(B40)=6,"-150","")&IF(DAY(B40)=10,"-200","")&IF(DAY(B40)=23,"-325","")&IF(DAY(B40)=30,"-500","")&IF((A40)="Mon","-125","")&IF((A40)="Fri","-325","")&IF((A40)="Fri","400",""))

Any ideas on how I can get all the to populate and sum appropriately?

Forgive my Non Excel Guru knowledge - trying to learn. :D

-Amy

Upvotes: 2

Views: 110

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60494

You are concatenating text strings that look like numbers. You probably want to be adding real numbers:

=SUM(IF(DAY(B40)=7,1474.22,0) + IF(DAY(B40)=22,0) + ...

although, whenever I see a formula as complex as what you have, I would consider looking for a different solution -- Vlookup comes to mind.

In addition, with a VLOOKUP table, you would have seen that you have some conflicts -- e.g: you list the same condition of B40=8 to return two different values; and the same condition of A40 = Fri, to also return two different values.

Upvotes: 0

user4039065
user4039065

Reputation:

If you take all of the options from your first working formula and change the method retrieving them, you will have a much more versatile worksheet that can easily accept new additions and schedule modifications.

    Family Bill Payment Schedule

  1. In a couple of unused columns to the right, pit in the day-of-month and the action that occurs. I'm using columns Y & Z. You have two events occurring on the 6th so I put them together.
  2. In a couple of other unused columns use the day-of-the-week and associated text.; I've used columns V & W. The default for Sunday is 1.
  3. In E36 use this formula,
          =TRIM(IFERROR(VLOOKUP(DAY(C36),$Y:$Z, 2, FALSE), "")&" "&IFERROR(VLOOKUP(WEEKDAY(C36),$V:$W, 2, FALSE), ""))
     
  4. Fill down as necessary.

If you want the day-of-the-week in column B, use =C36 and use a custom number format of ddd or dddd.

References:

  VLOOKUP function
  WEEKDAY function

Upvotes: 2

Related Questions