Formula Help

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
How about


Book1
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
Cell Formulas
RangeFormula
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
 
Upvote 0
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.
 
Upvote 0
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))),"")
 
Upvote 0
Thanks Fluff that worked perfectly. You're the MAN. Thanks for all of your help. Really really appreciate it.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top