Are you ready to take your data analysis skills in Google Sheets to the next level? Say hello to MINIFS in Array Formula, the powerful combination that allows you to find the minimum values based on multiple criteria. In this article, we’ll explore practical examples using a diverse dataset, so you can become an expert in using MINIFS to its full potential.
MINIFS Array Formula: Basic Examples for Expanded Results
Let’s start with some basic examples to get you comfortable with MINIFS in Array Formula. In our first example, we’ll find the lowest sales quantity of Apple and Orange. Here’s how you do it:
- Specify the criteria, “Apple” and “Orange,” in cells F2 and F3, respectively.
- Insert the following MINIFS Array Formula in cell G2:
=MAP(F2:F3, LAMBDA(r, MINIFS(D1:D, B1:B, r)))
And that’s it! The formula will return the results in the range G2:G3. By using MINIFS in Array Formula, you can easily find the lowest values based on multiple criteria.
In another example, let’s find the lowest sales quantity in December 2023 and January 2024. Here’s how you do it:
- Enter the criteria, 01/12/2023 and 01/01/2024, in cells F6 and F7, respectively.
- Enter the following MINIFS Array Formula in cell G6:
=ArrayFormula(MAP(F6:F7, LAMBDA(r, MINIFS(D1:D, EOMONTH(A1:A, -1)+1, r))))
In this case, we need to convert the dates in column A to the beginning of the month dates using the EOMONTH function. The formula will return the results in the range G6:G7.
These are just the tip of the iceberg when it comes to using MINIFS in Array Formula. Let’s dive into more advanced examples to further enhance your data analysis skills.
Advanced Examples: Unleashing the Full Potential
In the following examples, we’ll explore the versatility of MINIFS in Array Formula. First, we’ll look at an example that involves criteria from two columns: fruit names and their corresponding grades. The formula will return the lowest value that satisfies both the specified fruit name and its associated grade.
In another example, we’ll introduce OR logic within the grade column. This means the formula will return the lowest value that matches the specified fruit name in one column and either of the grades listed in the other column.
To see these examples in action, check out the screenshot below:
Now, let’s break down each example to understand how they work.
AND Logic in MINFS Array Formula in Google Sheets
Imagine you want to determine the lowest sales quantity of Grade 1 Apple and Orange. Here’s how to do it:
- Specify the “Apple” and “Orange” criteria in the cell range F2:F3.
- Specify the criteria, “Gr. 1,” in the cell range G2:G3.
- Insert the following MINIFS Array Formula in cell H2:
=MAP(F2:F3, G2:G3, LAMBDA(r_1, r_2, MINIFS(D1:D, B1:B, r_1, C1:C, r_2)))
The formula will return the results in the range H2:H3. By leveraging the power of AND logic, you can easily find the lowest values that satisfy multiple criteria.
OR Logic in MINFS Array Formula in Google Sheets
Now, let’s tackle a more complex scenario. Imagine you want to determine the lowest sales quantity of Apple and Orange, considering they can be either Grade 1 or Grade 2. Here’s how to do it:
- Specify the “Apple” and “Orange” criteria in the cell range F6:F7.
- Specify the criteria, “Gr. 1,” in the cell range G6:G7.
- Specify the criteria, “Gr. 2,” in the cell range H6:H7.
- Insert the following MINIFS Array Formula in cell I6:
=ArrayFormula(MAP(F6:F7, G6:G7, H6:H7, LAMBDA(r_1, r_2, r_3, MINIFS(D1:D, B1:B, r_1, REGEXMATCH(C1:C, r_2&"|"&r_3), TRUE))))
The formula will return the results in the range I6:I7. By using a helper function and cleverly manipulating the criteria, you can leverage OR logic within MINIFS to find the lowest values that match specific conditions.
With these advanced examples, you can truly harness the power of MINIFS in Array Formula to perform complex data analysis tasks.
Conclusion: Empower Your Data Analysis Skills!
Congratulations! You’ve now unlocked the full potential of MINIFS in Array Formula in Google Sheets. Armed with these techniques, you can confidently tackle complex data analysis tasks and extract valuable insights.
If you want to explore more advanced functions and techniques in Google Sheets, be sure to check out Crawlan.com, your ultimate resource for all things Google Sheets. It’s your go-to destination for expert tips, tutorials, and guides to help you become a data analysis ninja.
Now go forth and conquer your data with MINIFS in Array Formula!
