Formula Help
Results 1 to 6 of 6

Thread: Formula Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Location
    Philadelphia
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Help

    I created a template that will take data and count the data based on a number of criteria. For example, in cell A1 I have State entered, in cell B1 I have class code, C1 City, D1 deductible, E1 Zip Code, F1 cost new, G1 year and in H1 I have a formula that would count the criteria based on the first three labels in cells A:C and in this case state, class code and city and if it there is an auto that is in the same state, with the same class code and city it will count the number of time that appears. The data is in cell A2:G400

    Below is an example of some data

    State Class Code City Deductible Zip Code Cost New Year # of Units
    PA 33499 Philadelphia 2,000 60,000 2009
    Nj 23499 Cherry Hill 1,000 30,000 2017
    PA 50559 Erie 3,000 90,000 2016
    SC 40559 Spartanburg 3,000 80,000 2016 1
    PA 50559 Erie 2,000 50,000 2004 2
    NC 23499 Raligh 1,000 45,000 2012
    NJ 50599 Camden 3,000 100,000 2008 1
    PA 33499 Philadelphia 2,000 60,000 2009 2
    Nj 23499 Cherry Hill 1,000 30,000 2017 2
    NC 23499 Raligh 1,000 45,000 2012 2

    I have this formula in cell H2

    IF(A2="","",IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)=COUNTIFS($A$2:$A$400,A2,B$2:$B$400,B2,$C$2:$C$400,C2),COUNTIFS($A$2:$A$40 0,A2,B$2:$B$400,B2,$C$2:$C$400,C2),""))

    and I have it copied down to row 400.

    If the formula finds an auto that meets all the first three conditions set it will keep a running total but puts the total by the last one where that condition is true. If there are no autos that has all three criteria it will just enter 1.

    I created a macro that will take out the blanks which are the autos that has more than one auto that meets all three criteria so that I can then print it out without and have it rated. Some of the data can have two hundred rows but most of the time the will be multiple autos that might meet the criteria and say for example if the last auto meets the criteria it will be that total on the last auto which is why I created the macro to take out the blanks because it might only now show 10 row without the blanks as oppose to showing all two hundred.

    I was wondering if there was a way to not sure a macro to take out the blanks so that I could say create an area with the titles

    State Class Code City Deductible Zip Code Cost New Year # of Units

    and put formula in those cells I will put on only the auto where the count in cell H is 1 or higher.

    If it can't be done I am ok but thought I will try if its an easy fix.
    Ken

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Formula Help

    How about

    ABCDEFGHIJKLMNOPQR
    1State    Class CodeCityDeductibleZip CodeCost NewYear# of UnitsState    Class CodeCityDeductibleZip CodeCost NewYear
    2PA33499Philadelphia2,000111160,0002009SC40559Spartanburg30004444800002016
    3Nj23499Cherry1,000222230,0002017PA50559Erie20005555500002004
    4PA50559Erie3,000333390,0002016NJ50599Camden300077771000002008
    5SC40559Spartanburg3,000444480,00020161PA33499Philadelphia20008888600002009
    6PA50559Erie2,000555550,00020042Nj23499Cherry10009999300002017
    7NC23499Raligh1,000666645,0002012NC23499Raligh10001234450002012
    8NJ50599Camden3,0007777100,00020081
    9PA33499Philadelphia2,000888860,00020092
    10Nj23499Cherry1,000999930,00020172
    11NC23499Raligh1,000123445,00020122

    Totals



    Worksheet Formulas
    CellFormula
    L2=IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($H$2:$H$11>=1),ROWS($A$1:$A1))),"")



    Formula in L2 copied down & across
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Philadelphia
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help

    Thanks Fluff that worked great but I would also need to include the count in column H in the chart. They will need to know how many times a vehicle appeared. Can that be included.
    Ken

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Formula Help

    Use this formula instead, and copy across 1 more column
    =IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($H$2:$H$11<>""),ROWS($A$1:$A1))),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Location
    Philadelphia
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help

    Thanks Fluff that worked perfectly. You're the MAN. Thanks for all of your help. Really really appreciate it.
    Ken

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Formula Help

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •