Formula Help

[Tennisguuy]

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$400,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.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	State	Class Code	City	Deductible	Zip Code	Cost New	Year	# of Units				State	Class Code	City	Deductible	Zip Code	Cost New	Year
2	PA	33499	Philadelphia	2,000	1111	60,000	2009					SC	40559	Spartanburg	3000	4444	80000	2016
3	Nj	23499	Cherry	1,000	2222	30,000	2017					PA	50559	Erie	2000	5555	50000	2004
4	PA	50559	Erie	3,000	3333	90,000	2016					NJ	50599	Camden	3000	7777	100000	2008
5	SC	40559	Spartanburg	3,000	4444	80,000	2016	1				PA	33499	Philadelphia	2000	8888	60000	2009
6	PA	50559	Erie	2,000	5555	50,000	2004	2				Nj	23499	Cherry	1000	9999	30000	2017
7	NC	23499	Raligh	1,000	6666	45,000	2012					NC	23499	Raligh	1000	1234	45000	2012
8	NJ	50599	Camden	3,000	7777	100,000	2008	1
9	PA	33499	Philadelphia	2,000	8888	60,000	2009	2
10	Nj	23499	Cherry	1,000	9999	30,000	2017	2
11	NC	23499	Raligh	1,000	1234	45,000	2012	2
Totals

Cell Formulas
Range	Formula
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

 

[Tennisguuy]

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.

 

[Fluff]

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))),"")

 

[Tennisguuy]

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

 

[Fluff]

You're welcome & thanks for the feedback