Excel Formula Efficiency 4: SUMIFS & COUNTIFS Are Fast

[Galavia]

While your tutorials are helpful, it is also important to understand the math regarding time efficiency. In this area, you are teaching people bad math. If runner A completes a mile in 1 hour and runner B in only 5 minutes, runner B is 1100% faster than runner A. However, your formula suggests that runner B is 91.667% faster, not 1100%. This is exactly what you did with calculating time comparisons between formula calculations.

[ExcelIsFun]

@Galavia , The formula in the video shows the rate of change between the two calculating times. There was no mathematical ambiguity with the formula used in the video. The formula in the video showed the rate of change between the 2007 time and the 2003 time. The formula is End/Beg – 1 or (2007 average)/(2003 average) -1 is the Rate of Change in calculating time, a decrease in calculating time of about 0.98.

[ExcelIsFun]

@Galavia , In your example, the 91.67% represents the rate of change, a decrease of about 0.92.

[Mishkafofer]

Countifs is AWESOME

[tomprice494]

I have a spreadsheet with about 600 columns and 600 rows. Most of the cells are sumifs, looking for 4 criteria in a data set that is about 1300 rows. The workbook is painfully slow - taking many minutes just to open. I am curious what tricks for combining the data or sorting would speed it up. I love that sumifs looks for multiple criteria - but I don't really need to sum... there will only be one number for each. I need more of a lookup? Not sure how to make it faster.

[jayjavina]

Hi Mike,

Am trying to use the COUNTIFS function in one of my reports but still wont work on my condition.. I was thinking on how I can combine the function AND/OR with COUNTIFS.

I already asked for some assistance from the Mr.Excel website yet i didnt get any reply though i posted some supporting files.

Am I using the right function or is there something that i need to use?

[ExcelIsFun]

I have a video coming out later today called:

Extract Records Multiple Criteria (AND OR logic) Filter, Formula, Adv. Filter

The first part of the Formula example shows how to count with AND and OR criteria.

Send me a YouTube e-mail with the Mr Excel link and I will try to answer it!

[dorit1966]

Finally find the huge workbook at the bottom ,thanks for your quick reply. (:

[dorit1966]

first i would like to thank you for your great vdeos.

i think you should attached link for the correct workbook for each video, as I've lost looking for the right workbook.

thanks a gain.

[ExcelIsFun]

watch this video title to see how to best use the excelisfun channel:

excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks

[diegoluarca]

i dont know if you noticed but the second time you time the averageifs formula it does calculate faster, it shows 0.00293 compared to the first 0.028, just to let you know ;-)

[ExcelIsFun]

Thanks!

[cochondinde99]

You just MADE my day/week. I've been struggling with a huge file filled with sumproducts and this has solved my problems. THANK YOU THANK YOU THANK YOU!

[ExcelIsFun]

Your are welcome!! I am glad that it is helpful! Please rate 5 stars!

[SpotzB4URii]

Great tutorials.

I assume you can use dynamic named ranges with these formulas for data of changing sizes

[ExcelIsFun]

SUMIF/s and COUNTIF/s will work with dynamic ranges created with OFFSET function or created from Excel List / Table feature.

Whatever you use in the SUMIF/s and COUNTIF/s it has to be a range and not an array. See these videos for details about this fact:

Excel Magic Trick 440: Array Formulas Advanced Tips

and

Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays

[lineber2]

Great example of Countifs, Sumifs, and Averageifs formulas.

[ExcelIsFun]

I am glad that you liked them!

[keithrocknrolls]

Thanks for posting these videos, they're a great way of learning, and thanks for taking the time out to help me with conditional formatting in 2007.

[ExcelIsFun]

Dear keithrocknrolls,

You are welcome!

--excelisfun