4 Replies Latest reply: May 9, 2013 7:42 AM by Jerrold Green1
Y_M_L Level 1 Level 1 (0 points)

Lookin for a formula that will help me count when the value in Column A remains the same in Column C. Example below 3 rebates values remained the same so there is no loss or savings taking place. I need a count on my entire sheet to see how often this is occuring. Any help appreciated!

 

Column(A) Prior Quater Rebate   Column(B) Current Quarter     Column(C) Current Quarter Rebate

150.00                                         2013Q4                              175.00

153.00                                         2013Q4                              160.00

150.00                                         2013Q4                              150.00

120.00                                         2013Q4                              155.00

120.00                                         2013Q4                              115.00

150.00                                         2013Q4                              150.00

125.00                                         2013Q4                              125.00                                            


MacBook
  • Wayne Contello Level 6 Level 6 (15,515 points)

    the easiest way (in my opinion) is the sum the values in column A, then sum the values in column C then take the difference.

     

    Add a footer row to your table by selecting the table then click the add footer button in the tool bar (top right):

    Screen Shot 2013-05-08 at 9.20.59 PM.png

     

    In the footer add the formulas as follows:

     

    Screen Shot 2013-05-08 at 9.23.36 PM.png

    A10=SUM(A)

    C10=SUM(C)

    D10=C10-A10

     

    if you want a more binary answer in D10 you could also enter:

    D10=IF((C10-A10) >0, "You saved money", "You did not save any money")

     

    Screen Shot 2013-05-08 at 9.26.46 PM.png

  • jaxjason Level 4 Level 4 (3,520 points)

    wayne, I think the OP was asking for a count of how often it has happend that A and C were equal. Couldnt we do that by adding an equation in D that simple read =A2=C2. Then in your footer, add a =countif(D:D, True)? That would give an actual count of number of times they were equal.

     

    Jason

  • Wayne Contello Level 6 Level 6 (15,515 points)

    Jax,

     

    I was keying off the words:

     

    same so there is no loss or savings taking place.

     

    to lead me to a slightly different solution thinking that the end goal may not really be the count of how many items were a match but ultiimately whether there was a savings realized.

     

    Wayne

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    jaxjason wrote:

     

    wayne, I think the OP was asking for a count of how often it has happend that A and C were equal. Couldnt we do that by adding an equation in D that simple read =A2=C2. Then in your footer, add a =countif(D:D, True)? That would give an actual count of number of times they were equal.

     

    Jason

    Exactly how I read it.

     

    Jerry