3 Replies Latest reply: Sep 1, 2013 8:55 PM by Barry
chick.pea Level 1 Level 1 (0 points)

I'm trying to keep track of payments made, according to date.

 

I have one column where I enter dates.

 

Can I associate these date cells to a fixed $ value, so that each time I enter a new date the SUM cell will automatically update?

 

I hope so...

 

Thanks!

  • 1. Re: How to associate a fixed $ value to a date cell?
    Wayne Contello Level 6 Level 6 (13,615 points)

    Maybe this is what you are thinking?:

     

    Screen Shot 2013-09-01 at 10.39.12 PM.png

    C3=COUNTA(A)*C2

     

    this is shorthand for in cell C3 type the text "=COUNTA(A)*C2" without the double quotes

     

    this counts the number of cells in column A with something (doesn't even have to be a date), then multiplies that count times a fixed payment amount in cell C2

     

    In my example I did not format the the amount and total cells as currency.  You can do this by selecting those cells then use the cell inspector to format as currency:

    Screen Shot 2013-09-01 at 10.42.37 PM.png

  • 2. Re: How to associate a fixed $ value to a date cell?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    CP,

     

    "Can I associate these date cells to a fixed $ value...?"

     

    If I'm reading this question correctly, you would count the number of dates entered and multiply the count by a fixed dollar figure to calculate the total. Is this what you had in mind?

     

    If so, a sample total calculation might be:

     

    =COUNT(date-entry-range)*500.00 and format this total cell as Currency.

     

    In this example you would have a result of $500 times the number of entries in the date-entry-range of cells.

     

    Jerry

  • 3. Re: How to associate a fixed $ value to a date cell?
    Barry Level 7 Level 7 (29,180 points)

    HI cp,

     

    By "associate these cells to a fixed $ value," I assume you mean that fixed value will be added to the sum as each date in sentered.

     

    Here are two examples, one showing the fixed amount in the adjacent column when the date (or anything) is entered in column A, the other showing only the total.

     

    Both use a second table to hold the fixed value, making it easy to edit, if necessary.

    Screen Shot 2013-09-01 at 8.42.47 PM.png

    Formulas:

    (Method 1)

     

    B2, and filled down to B10: =IF(LEN(A)>0,Fixed value :: A$2,"")

     

    LEN(A) returns the length (in character) of the entry in the cell in this row of column A. If anything is entered, the IF condition is TRUE, and IF returns the value in A2 of the smaller table. If nothing has been entered, the condition is FALSE, and IF returns a null string to the cell.

     

    B11: =SUM(B)

     

    Row 11 is a Footer row, and is not included in the cells to be summed.

     

    (Method 2)

     

    D11: =Fixed value :: A2*COUNTA($A)

     

    COUNTA counts the number of entries in column A of the Main table, then multiplies the fixed value in the small table by the result of the count and returns the result.

     

    This formula may be placed in any cell.

     

    (Both methods)

     

    The cell reference to Fixed Value::A2 (or Fixed Value::A$2) may be replaced with the actual value, and the second table eliminated, if desired.

     

    Regards,

    Barry