Ctrl + Shift + Enter: Excel Array Formulas 12: SUMIFS, COUNTIFS, SUMIF, COUNTIF, AVERAGEIF

EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!

This video covers Function Arguments That Can't Handle Array Operations:
1. (00:50 min) What to do if SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS can't handle array calculations in range argument?
2. (00:50 min) Formula goal: Add with year criteria that is mismatched against serial number data.
3. (01:47 and 05:17 min) range and criteria_range arguments can't handle arrays.
4. (03:12 and 05:35 min) 4 Formula substitutes for SUMIF and YEAR array calculation in range argument.
5. (05:45 min) Formula goal: Count with year criteria that is mismatched against serial number data.
6. (06:04 min) 4 Formula substitutes for COUNTIF and YEAR array calculation in range argument.
7. (06:08 min) Formula goal: Averaging with year criteria that is mismatched against serial number data.
8. (06:08 min) 5 Formula substitutes for AVERAGEIF and YEAR array calculation in range argument.
(07:25 min) Timing Results for adding, counting and averaging formulas.
9. (08:53 min) Criteria argument in COUNTIF and similar functions can handle arrays.
10. (10:22 min) Range argument in COUNTIF and similar functions can handle ranges created by INDEX or OFFSET functions.
THIS VIDEO SERIES AT YOUTUBE IS THE SAME AS THE DVD FROM EXCELISFUN. THESE VIDEOS ARE BEING GIVEN AWAY FOR FREE AT YOUTUBE. SUPPORT THE CAUSE BY GOING TO AMAZON AND BUYING THE BOOK.
EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!
Ctrl+Shift+Enter: A Book About Building Efficient Formulas, Advanced Formulas, and Array Formulas for Data Analysis and Calculating Problems
Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions. Among the practical applications surveyed include how to extract data from tables and unique lists, how to get results that match any criteria, and how to utilize various methods for unique counts. This book contains 529 screen shots.

[zeetech]

You are my savior ! I was stuck with the year problem for a long time until I found you! Thank You!

[excelisfun]

Ctrl + Shift + Enter: Excel Array Formulas 12: SUMIFS, COUNTIFS, SUMIF, COUNTIF, AVERAGEIF.
Download Excel file link below this video.
There are 26 videos in this playlist series.
This video is the same as the video on the DVD, but it is being given away for free here at YouTube.
To Support the cause, go to Amazon and buy the book or DVD. Title is: Ctrl + Shift + Enter: Mastering Excel Array Formulas.
Excel Array Formulas work the SAME in any version of Excel!!!

[msifre]

I bought you books years ago and I wacth your fantastic videos. Many thanks

[shooterlt]

EIF!! Never mind I found it!! Magic Trick 1024 explained the issue perfectly. Thanks so much!!

[shooterlt]

Hey EIF! Absolutely love your work. I'm a semi advanced excel user and your vids are great when I know Excel will have the logic I need but can't find it.

I'm sure you have this somewhere but I can't seem to find it: when you put a range in a formula and shift+F4 to lock it, how do you do this in recent versions where it automatically recognises the 'table' column and names it as such? I.e. 'Bills[date] rather than the range reference, B2:B150

[JosipZekoRijeka]

I would like to ask a question.
You got amasing videos, and I love to learn from it.
Here is my question:

When you talk about Timing and fastest way to calculate using Helper Column. Is it maybe slower to loading Excel workbook every time you open?

Thx for excelent job!

[ExcelInstructor]

can you help with dax please?
I have a table loaded into data model, its just 1 table.
in this table, there are columns A, B, C, D, E with values, zero's and nulls.
I tried doing average column, averageX where I specified the table, then added 5 columns to each other. and I even did it manually with sumx and division by 5
each time I got a different result, when I put my measures into the PT.

[tomash]

Hi Mike if you review this posts I ran into trouble just opening your file and finding some of your formulas did not work



 returns correctly

{3, 8;3, 6;2, 3;2, 6}

but 

returns zero

and same this type of formula returns 0