Multiple IF Arguments

**Chris-TT** · 12-22-2010, 03:32 PM

I’m trying to get Excel to see if a cell contains the word ‘Sunday’ if it does it counts back the days to Sunday and displays the results of a sum. This seems to be working fine, with one or two IF statements, but as soon as I use any more than that I get an error. I need to go back upto 7 days. This is the calculation I was trying to use:

IF(B33="Sunday",SUM(L34:L34)," ",IF B32="Sunday",SUM(L33:L34)," ",IF B31="Sunday",SUM(L32:L34)," ",IF B30="Sunday",SUM(L31:L34)," ",IF B29="Sunday",SUM(L30:L34)," ",IF B28="Sunday",SUM(L29:L34)," ",IF B27="Sunday",SUM(L28:L34)," ")

Any help much appreciated.

**zbor** · 12-22-2010, 03:38 PM

Maybe this:

=SUM(OFFSET(L27, MATCH("Sunday", $B$27:$B$33, 0), ):L34)

If there is no Sunday you'll get #N/A (meaning, you must type Sunday in range $B$27:$B$33)

**NBVC** · 12-22-2010, 03:39 PM

Does this work for you?

=SUM(INDEX($L$28:$L$34,MATCH("Sunday",$B$28:$B$34,0)+1):$L$34)

Just adjust the L28 and B28 to match the top cell of the B range you are looking at.

**Chris-TT** · 12-22-2010, 03:46 PM

Awesome, many thanks zbor, I just tried it and it works a treat!

**Chris-TT** · 12-31-2010, 01:21 PM

Just to elaborate a bit further I’m having trouble with one more small thing to do with this formula. I want a “-“ to appear if fields are empty. Now this works fine if the last day of the month is a Sunday, however I’m trying to make it so if the fields are blank it will always show “-“.

Here is the formula that works but only on the Sunday:

=IF(B34="Sunday",IF(SUM(L28:L34)=0,"-",SUM(L28:L34)),SUM(OFFSET(L28,MATCH("Sunday",B28:B34,0),):L34))

And here is what I thought I needed to do to correct the problem, but its saying there are too many arguments:

=IF(SUM(L28:L34)=0,"-",IF(B34="Sunday",SUM(L28:L34)),SUM(OFFSET(L28,MATCH("Sunday",B28:B34,0),):L34))

**NBVC** · 12-31-2010, 01:32 PM

Try:

=IF(SUM(L28:L34)=0,"-",IF(B34="Sunday",SUM(L28:L34),SUM(OFFSET(L28,MATCH("Sunday",B28:B34,0),):L34)))

**Chris-TT** · 12-31-2010, 01:35 PM

Many thanks! [= Works perfectly.

Multiple IF Arguments

LinkBack

Thread Tools

Rate This Thread

Display

Multiple IF Arguments

Re: Help Dealing With Multiple IF Arguments

Re: Help Dealing With Multiple IF Arguments

Re: Help Dealing With Multiple IF Arguments

Re: Multiple IF Arguments

Re: Multiple IF Arguments

Re: Multiple IF Arguments

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions