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
  • 1. Re: How to count duplicates
    Wayne Contello Level 6 Level 6 (13,620 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

  • 2. Re: How to count duplicates
    jaxjason Level 4 Level 4 (3,325 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

  • 3. Re: How to count duplicates
    Wayne Contello Level 6 Level 6 (13,620 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

  • 4. Re: How to count duplicates
    Jerrold Green1 Level 7 Level 7 (28,995 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