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

[@minecraftmike5193]

Love you Mike! Can’t believe the enthusiasm you have for Excel and teaching, it’s something very special ❤

[@excelisfun]

I am happy to make special fun Excel videos for you and the rest of the Team!!!!

[@Excelambda]

Great video!! ✌ If I will find time this week I will publish a study about my FRQ function, concept plus a tone of examples. - can deal with lower or upper limits (pivot or countifs functionality + frequency functionality in one) - can calculate frequencies of non numeric values (ignored by FREQUENCY) - results can have same framework of bins array (if bins array is 2D, FREQUENCY results are always a clm vector) - calculates for any bins array and/or sorted unique bins intervals that can be listed next to the frequencies.....and more ✌😊

[@excelisfun]

You are working for Microsoft now, right lol You, da man!!!!!! Excel is Lambda Fun!!!!

[@Excelambda]

@@excelisfun Not yet ! 😂

[@vishal.pandey2001]

Mr ExcelisMagic Sir , You are the greatest excel lover ever known to me. Though the problem has been solved earlier , you always brings up alternative with definite explanation as to what, why and how Please never stop posting as it brings in me passion after always watching your videos

[@excelisfun]

I am very happy that you enjoy what I post. It has been 15 years 362 days so far that I have posted, and I plan to keep doing it forever, or as long as I can (which ever comes first) : ) : )

[@williamarthur4801]

it may not gave been the main purpose of the video but I'd never thought of using find and replace on a pivot, what joy 😊😊

[@excelisfun]

Yes!!! Ctrl + H Rules in Excel and Word : )

[@HusseinKorish]

Wow ... i spent alot of time to use grouping in pivot table reports ... and my numbers included decimals ... and of course it didn't work correctly ...now i konw ... thanks Mike

[@excelisfun]

Yes, it is important to know this fact about Pivots : ) : )

[@chrism9037]

Thanks Mike, another great video!!!!! I didn't know about the upper limit issue in pivot tables.

[@excelisfun]

The Microsoft programmers had to pick one side to include, and they chose the lower limit. For most projects it doesn't matter because the patter will still be revealed, but if you need to include upper limit... : )

[@roywilson9580]

Thanks Mike, another great video. I have noticed that Excel can do some very odd things to the unwarey so it is always a pretty good idea to double check upper bounds, lower bounds and any calculation that includes conditionals or many nested functions.

[@excelisfun]

I 100% agree. Very smart, Roy! I always do projects at least two ways to try assure that things are correct.

[@roywilson9580]

@@excelisfun I do the same and have even been known to resort to pen, paper and brain-power when calcution results look off but is probably just me showing my age :D

[@excelisfun]

@@roywilson9580I am that way too: sometimes paper and pencil to check : )

[@syrophenikan]

Fantastic! I learn something new EVERY time!!!

[@excelisfun]

Awesome!!! You and I both: I learn something new each time too : )

[@markpodesta4605]

Thank you Mike. Can you also create similar dynamic pivot tables by using GROUPBY and PIVOTBY?

[@excelisfun]

There is not an automatic groupby feature in those functions, but it would be cool if Microsoft added one : )

[@viktorasgolubevas2386]

​@@excelisfun a raw and not very tested version for thought and improvement could be =GROUPBY(CEILING.MATH(C6:C25 , 500) , C6:C25 , COUNT) and "opposite" option for include/exclude =GROUPBY(FLOOR.MATH(C6:C25 , 500) , C6:C25 , COUNT)

[@kamal3r]

Informative and new way for me

[@excelisfun]

Glad this helps : )

[@SyedMuzammilMahasanShahi]

Thanks amazing Mike for this EXCELlent video.

[@excelisfun]

You are most welcome, Fellow EXCELlent Teacher : ) : ) : )

[@peterjarabek5541]

THANKS. EXCELLENT VIDEO & EXPLANATION:)

[@FutureCommentary1]

My takeaway is that I didn't know one could group categories in pivot table the way you did. And I didn't know the frequency function. I'm more likely to use it than countifs in similar cases.

[@excelisfun]

You inclination is good because COUNTIFS always gives the most flexibility with creating just the categories that you want.

[@dhaval1489]

I think using =Counta(filter()) would also do the trick in every situation, but it is bit slow in performance

[@excelisfun]

You are right. COUNTIFS, SUMIFS and the like will almost always calculate faster than direct array operations.

[@Chaingangsoldiernit]

Super helpful. Thank you!

[@excelisfun]

You are welcome!!!

[@prabhusundar3006]

Great video Mike, We can explore the same using sumproduct as well..

[@excelisfun]

You can use SUMPRODUCT, but as I researched and wrote about in my 2012 book, Ctrl + Shift + Enter: Mastering Excel Array Formulas, COUNTIFS will almost always calculate more quickly than SUMPRODUCT because COUNTIFS use underlying code whereas SUMPRODUCT has to make direct array calculations, row-by-row.

[@excelisfun]

Here is how you would do it with SUMPRODUCT: =SUMPRODUCT(--($C$6:$C$25>=E25),--($C$6:$C$25

[@excelisfun]

It took me about 20 minutes to get the new file uploaded, but it is there now.

[@prabhusundar3006]

@@excelisfun thank you Mike..... I've read your cntr+ shift+enter book back in 2015 and have been following you since 2014....you have been my greatest inspiration and have learnt so much following you.... Thank you for everything... 😊

[@excelisfun]

@@prabhusundar3006 You are welcome for everything : )

[@rajeshmajumdar4999]

Thank you so much!

[@excelisfun]

You are welcome so much!!!

[@johnborg5419]

Thanks Mike.

[@excelisfun]

You are welcome, Formula Guy John!!!

[@gvitullib]

Wao! that trick over the pivot table doing the labels thing, I didn't know it. Mike., kudos men!

[@excelisfun]

Ctrl + H is fun : )

[@nadermounir8228]

Great video Mike 📹 👍. One question : i didnt quite get what u mean that the decimal numbers in pivot tables are not included ?

[@excelisfun]

Decimals numbers induce the Pivot to group this way. If you have integers, the categories are different: there is no duplicate numbers in the upper limit of one category and the lower limit of the next category. Categories go like this: 0-499, 500-999 and so on, rather than 0-500, 500-1000 and so on.

[@excelisfun]

I added an integer example to the download workbook file : )

[@excelisfun]

It took me about 20 minutes to get the new file uploaded, but it is there now.

[@nadermounir8228]

@@excelisfun ah I see now that is very interesting I didn't know that before.

[@nadermounir8228]

@@excelisfun thank u so much Mike for adding the interger example. This makes everything very clear 👌

[@tofikqureshi6507]

Hello Sir Pls help me I'm facing the one condition use Google sheet make pivot table need click pivot table showing data as same sheet not new sheet data visible same sheet pls❤help

[@excelisfun]

I am sorry, I do not know how to use Google Sheets : (

[@roncoderre]

Hey, Mike..... Maybe something like this to enhance the FREQUENCY approach to skip the extra category?: =CHOOSEROWS(FREQUENCY(C6:C25,F15:F18),SEQUENCE(1,4))

[@nguyentuan826]

you can use =drop(FREQUENCY(C6:C25,F15:F18), -1) to remove extra category,

[@excelisfun]

Thanks, Ron!! Thank extra category always gives people trouble.

[@excelisfun]

@@nguyentuan826 Thanks Tuan Nguyen!! Microsoft is so good to us with DROD and TAKE : )

[@timestoryx]

wow great :D

[@excelisfun]

Glad it is great for you !!!!

[@Rahul-2914]

Need some corrections in countif formula because pivot table has different values

[@excelisfun]

That does happen often. The specific details always lead to a solution ; )

[@excelmaster1328]

Sir what is the work in company for Excel. What work does company give for Excel jobs

[@joshpeters813]

First😊

[@excelisfun]

First Place Trophy for you : ) : )

[@txreal2]

Changed 500.00 to 500, PivotTable still not working. So it's not just the decimal format or something else? Thanks.

[@excelisfun]

I do not know what you mean. What is not working? I just added an example of integer and decimal numbers to the download file on the 1849 (an) worksheet tab. You can check that out.

[@excelisfun]

If you tried Number Formatting, you might be missing a crucial fundamental concept in Excel: Number Formatting is a façade - Number Formatting never changes the actual number, it just displays it on the surface in a different way. Number Formatting is not seen by formulas and PivotTables - Pivots and Formulas always see and act on the underlying number.

[@MohdSaif-oq3ow]

How to download free Excel laptop plz share me link