Don’t Use PivotTables. Use COUNTIFS or FREQUNCY Functions Instead. Excel Magic Trick 1849

now we've been given this data set and this goal we need to count the sales between an upper and a lower limit but we've been given very specific lower and upper limits the lower limit cannot be included but the upper limit so if there's a 500 in that category right there it has to be counted well I'm going to use a pivot table because that's the easiest way I click in a single cell insert pivot table or I can use the keyboard alt nvt I want to put it in an existing sheet let's put it there click okay let's take sales drag it down to rows and instantly it does what it's supposed to it gives us a unique list but we can rightclick group it shows us the Min and the Max and the increment I want want this to be neat and tidy so we'll start at zero and we'll go up to we'll round it up to 2,000 and we'll have increments of 500 when I click okay rightclick Group by I think I left out a zero click okay that's looking much better I'm going to drag sales down to values and Bam there's our count but I got to count of five for 0 to 5 100 I see 1 2 3 4 5 6 so if I really need to include the upper limit you can't use a pivot table when the data has decimals the grouping feature does not include the upper limit that's when we switch over to formulas we can use count ifs not count if but the one with the S criteria range well we have an upper and lower limit and we have to repeat the sales column twice comma criteria this is the lower limit so I have to say greater than in double quotes ersan and if I were using a single cell and copying it I'd just put that and lock that but I don't want that I want a dynamic spilled array formula so I'm going to highlight all four conditions so count FS will spill all four answers comma we repeat the sales column comma and the upper l whatever we're looking at has to be less than or equal to in double quotes anderan highlight the upper limits and when I close parentheses controll enter bam we get the correct count of six because in this category 500 is included now a lot of times when I have reports like this I'll put a label off to the side something like this we have our lower limits and then we join it to and we want to put text we want sales to be greater than the equal sign is not on the lower end sales are in the middle sales simultaneously have to be less than or equal to the upper limit when I highlight and hit enter there are labels that say exactly what's going on now these are great I tend to put something like not included or upper limit not included now here's a great trick in a pivot table what if you wanted to do something similar I'm highlighting just the row area of the pivot table up to Home find or controll H and guess what I'm going to search for a dash and in its place I'm going to replace that with that click replace all click okay close and now our pivot table also has explicit labels now there is one other formula method and it invol evolves using the frequency function now the thing about the frequency function is you got to be clear on how the categories are created now the upper limits are included but there's an upper and lower category that are different than the middle ones these middle ones are exactly like the ones we did for the count FS but because when they designed frequency they wanted to catch every possible value and they didn't trust people to put the correct upper limits they have the lower category as everything equal to or less than this so it could go down to whatever negative number you wanted and then they always included one last upper category to catch any numbers that might be bigger than the upper limit that you give it so frequency you just need the data comma and the upper limits controll enter it's a dynamic spilled array formula and it automatically adds that last one if I put 5,000 here sure enough it counts contrl Z if I put minus 500 sure enough that catches it contrl Z so we have frequency as a formula count ifs and pivot tables and really the most important thing about this video which is a surprise to a lot of people if your numbers have decimals and you group that upper limit is not included all right we'll see you next Exel magic trick

Channel: ExcelIsFun

Views: 22,003

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1849, EMT, EMT 1849, Microsoft 365 Excel, Excel 365, FREQUENCY Array Function, PivotTable, COUNTIFS function, Frequency distributions for quantitative data, upper and lower limits, upper limit not included in PivotTable Grouping. PivotTable Grouping

Id: mnFq91rRibc

Length: 5min 40sec (340 seconds)

Published: Mon Feb 05 2024