Include cell in calcuation based on other cell value

[pjhtechnology]

The workbook I'm creating needs to be able to be used in Excel 2003 (I am developing using 2007) - I know the answer to my question in 2007 is to use SUMIFS

I have a Banking Forecast spreadsheet - At the beginning of the month, I know what payments have to be made each day of the that month and I have a date associated with each payment - the specific date when a payment is made will change month-to-month and the amount to be paid will vary - I want to total all of the payments made until TODAY - I know I can use a series of if conditions (i.e., +IF(a2<=TODAY(),b2,+(a3<+TODAY(),b3 ) - the problem is that the user needs to be able to delete and/or add columns

For example:

<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 0.3in; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1" vAlign=top width=29>

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1" vAlign=top width=115>

A<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1" vAlign=top width=115>

B<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1" vAlign=top width=115>

C<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1" vAlign=top width=115>

D<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1" vAlign=top width=115>

E<o></o>

​

</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 0.3in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=29>

1<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

Electric<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

Qtrly Dues<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

Payroll<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

Phone<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

Running Total<o></o>

​

</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 0.3in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=29>

2<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

3/2<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

3/2<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

3/5<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

3/10<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

<o></o>

​

</TD></TR><TR style="mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 0.3in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=29>

3<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

$500.00<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

$0.00<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

$2540.00<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

$189.00<o></o>

​

</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 1.2in; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=115>

<o></o>​

</TD></TR></TBODY></TABLE>​

All the amounts and dates are determined on 3/1

On 3/2 the Run Total would be $500.00, on 3/5 it would be $3040.00 and on 3/10 it would be $3229.00

The user needs to be able to insert columns for unexpected payments and would like to delete columns where the amount for the month is $0.00

Thanks

 

[Andrew Poulsom]

Welcome to the Board.

I don't know why you would need the SUMIFS function. This should work:

=SUMIF(A3:D5,"<="&TODAY(),A6:D8)

It will continue to work if columns are inserted or deleted between columns A and D.

 

[Andrew Poulsom]

I got merged cells when I pasted your sample data from the Board. The formula should be:

=SUMIF(A2:D2,"<="&TODAY(),A3:D3)

 

[ukmikeb]

Hi

An alternative would be :-

=SUM(IF($A$2:OFFSET($E$2,0,-1)<=TODAY(),$A$3:OFFSET($E$3,0,-1),0))

enter with - Control-Shift-Enter

And this would cover against users inserting/deleting a block of 3 cells next to the "Running Total" column.

hth

Mike

Andrew - Are you sure yours will work in all cases? Try inserting on the "Total" column! My attempts failed to change both types of column references.

 

[Andrew Poulsom]

Andrew - Are you sure yours will work in all cases? Try inserting on the "Total" column! My attempts failed to change both types of column references.

As I said:

It will continue to work if columns are inserted or deleted between columns A and D.

 

[pjhtechnology]

Doh!!! Thanks - my brain is just about Excelled out building this project workbook - can't imagine what I was thinking not to just go to SUMIF - now I'll have to research what's the advantage, reason for, differences between SUMIF and SUMIFS besides the syntax - thanks again!!!