# Thread: Formula Help Thanks: 0 Likes: 0

1. ## 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.

2. ## Re: Formula Help

ABCDEFGHIJKLMNOPQR
1State    Class CodeCityDeductibleZip CodeCost NewYear# of UnitsState    Class CodeCityDeductibleZip CodeCost NewYear
3Nj23499Cherry1,000222230,0002017PA50559Erie20005555500002004
4PA50559Erie3,000333390,0002016NJ50599Camden300077771000002008
6PA50559Erie2,000555550,00020042Nj23499Cherry10009999300002017
7NC23499Raligh1,000666645,0002012NC23499Raligh10001234450002012
8NJ50599Camden3,0007777100,00020081
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

3. ## 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.

4. ## 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))),"")

5. ## Re: Formula Help

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

6. ## Re: Formula Help

You're welcome & thanks for the feedback