Skip navigation

How to count duplicates

224 Views 4 Replies Latest reply: May 9, 2013 7:42 AM by Jerrold Green1 RSS
Y_M_L Calculating status...
Currently Being Moderated
May 8, 2013 3:50 PM

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 (12,660 points)
    Currently Being Moderated
    May 8, 2013 7:27 PM (in response to Y_M_L)

    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,320 points)
    Currently Being Moderated
    May 9, 2013 5:33 AM (in response to Wayne Contello)

    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 (12,660 points)
    Currently Being Moderated
    May 9, 2013 6:02 AM (in response to jaxjason)

    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 (28,215 points)
    Currently Being Moderated
    May 9, 2013 7:42 AM (in response to jaxjason)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.