How to Count Rows with OR condition with Excel Array Formula - SUMPRODUCT & FREQUENCY (Part 1/3)

preview_player
Показать описание

Excel Array functions can be difficult to get the hang of. This video explains the logic behind arrays in simple steps while attempting to solve a problem (counting rows based on OR condition).

It also shows you a simple method you can use when you need to come up with other complex formulas.

Key Learning Points:
- Unique Challenges Across Rows: Learn how to deal with unique challenges when working with data across rows, not just columns.
- Crafting Advanced Formulas: Discover the thought process and steps involved in creating complex array formulas from scratch.
- Utilizing Key Excel Functions: Get familiar with crucial Excel functions like COUNTIF, SUMPRODUCT, MIN, and FREQUENCY, and how they can be employed in array formulas.
- Sample Data Analysis: Watch a practical example where we analyze workshops won by different companies, applying advanced array formulas to extract insights.
- Breaking Down Complex Formulas: Learn how to break down and understand large array formulas, making them more approachable and manageable.
- Matrix Manipulation Techniques: Explore techniques to manipulate matrices in Excel, transforming data to achieve desired outcomes.
- Robust Formula Development: Understand the limitations and strengths of different approaches, ensuring your formulas are robust and reliable.

This video is part 1 of a 3 video series. In each of the videos of the series I use a different Excel array formula to get to the same result.

In this video I show you how you can come up with an array formula that counts the rows if at least one of the lookup fields from the criteria list is in that row. It's an OR logical test involving multiple criteria. It shows a great use of the FREQUENCY function, the SUMPRODUCT formula and the ROW function.

In addition to solving this problem, I take you through the steps you can take to break down complex array formulas and show you tricks you can use to "follow" your formulas' results which helps you come up with other functions that can help you get to your answer.

Series 2 of 3, will introduce you to Excel's MMULT function (Matrix Multipication).

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel
Рекомендации по теме
Комментарии
Автор

Hi Leila.. this is an excellent 3-part series for anyone wanting to understand array formulas and matrix manipulations. I learned a lot. Thanks for Part 1 of 3. Thumbs up!

wayneedmondson
Автор

Great to see you here at YouTube making videos about such fun Excel Array Formula topics! : )

excelisfun
Автор

Dear madam, so far you're the best excel teacher so far, either offline teacher or online teacher at least for me. I have been watch for at least 10 videos yesterday and was able to understand. But today for Array formula is a bit confusing in term of you're using language but i swear only this time. Like when you're explaining about Frequency formula which showed Array_bin syntax as 1 and 7 and the result was 17. Your explaining is "Anything goes up 1 in what is counted" that makes me repeat this duration for almost 20 times or more maybe cause i'm fixed by 1 number. So that i counted by myself finally understand what it means. Also the rest was connected after that. Like just for advice, instead saying "Anything goes up 1" how about use more consistent words like in the frequency itself, the ways are (3 rows from array_bin 1 and 7) are "Less than equal", "In between", "More than". So yeah basically just language problem, overall your teaching goes very very well done. Thanks madam.

hexlet
Автор

Though the array formula initially looked mind bogglingly complex, it was easy to follow once you explained it. Thanks for the video and I really appreciate the hard work that went into it :)

surjagain
Автор

Amongst the many great things about your videos Leila is the free workbooks.
Thank you 🙏

rory
Автор

You are really a great teacher and good method of teaching .

azizniazi
Автор

great videos, very detailed and you speak slow enough to allow us to absorb and try it for ourselves. the example worksheets are very helpful. excellent job. thanks

EOO-Stand
Автор

I literally fell asleep three times watching this. Not teachers fault, but this is so hard - speaking as ex-investment banker excel pro. Also love her videos :)

JQ
Автор

awesome! the two greatest ever excel god is here on the expert is a small word for u u r out of this world!!i really appreciate from the bottom of my heart

jackychawla
Автор

Hi Leila, can we use Solver Tab to evaluate results easier than formula (Sumproduct+frequency+min/max …)

teoxengineer
Автор

Hi

Please can you tell my how can i count a particular text in a column excluding certain rows, eg rows s2:s500 and count rows that say on track excluding text in row s4 / s13/s18

mildredsouza
Автор

Your explanation is spot on.Really appreciate your effort forward to similar Videos on VBA as well

anandsingh
Автор

ooo wait,
lemme give her hearts for this.
love u mam:)

kritikarya
Автор

Dear Madam, is there any method to count number of trues without helper column, I mean if we have data in two column say A and B and we are matching both, I need a single formula to get the count of trues in a single cell, Kindly reply

sajadmk
Автор

Thanks madam... i really like your session i learnt so many thing which i don't know earlier..
i really appreciate your efforts
one request i hav for you that can you please pan camera on the worksheet in zoom mode becoz sometimes it very difficult to make out the cell text...
Thanks once again...

amardeep
Автор

Hi, your excel videos are awesome. How did you hide and unhide those 2 helper columns in this video?

psavinda
Автор

4:00 If you want to find out who WON, why do you check against 2nd and 3rd place? What am I missing?

holister
Автор

Hi leila..I am trying to calculate response time in a large data set. I have the time a ping is sent out and then several rows that contain the next time stamps to see how the ping traveled. For a set on pings, the common identifier is the ping id. So in my data set, I want to see how I can found the min timestamp for a particular ping id. Essentially i am trying to calculate response time from when the ping was first sent and finally picked. Dataset has information for multiple pings.

swativish
Автор

Hello I like all your videos. you have inspired me to learn excel and am having fun. I work in a security agency and I need to work on roasters. can you make new videos on roasters. thank you

pargalrangers
Автор

Does anyone know a formula for arraying a =MAX(A1:A4)? I have rows down the page that I need to =MAX, but when I try I end up getting the max of the whole table, not just the row.

RAHowarth
join shbcf.ru