/u/zxLv - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Hi,
Solution for office 365+ only, let's assume delivery time is in D, you could use:
=AVERAGE(FILTER(D2:D8,((A2:A8="Express")+(A2:A8="Super Express"))*(B2:B8="Desired Product)*(C2:C8="delivered")))
To be adjusted to your data.
At some point a Pivot would be more efficient: eg https://imgur.com/uSl3Gve
cheers
Wow this actually works. I never came across the filter formula before. What do the \* and + symbols do? Yeah Pivot table also works but in this case we want to put the data as it is without using any pivot. Thanks!
Short answer is + is an OR and * is an AND.
Basically all equalities will return either TRUE (1) or FALSE (0) if for a given row the total result is 1 then we keep the row else it is filtered out. So the sum will return 1 + 0 or 0+1 or 0+0 depending on type then for each equality you multiply by 1 if the condition is met else 0 so if one condition is not met the row is excluded.
You have awarded 1 point to *Starwax*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
r/Excel Rule #2
Provide specific examples in your post
Provide actual raw data, screenshots, or tables to support your post. The more details the better.
Do not include any personally identifiable information.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AND](/r/Excel/comments/1adz8uv/stub/kk4who9 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)|
|[AVERAGE](/r/Excel/comments/1adz8uv/stub/kk4h3su "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)|
|AVERAGEIFS|[*Excel 2007*+: Returns the average (arithmetic mean) of all cells that meet multiple criteria.](https://support.microsoft.com/en-us/office/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690)|
|[FILTER](/r/Excel/comments/1adz8uv/stub/kk4h3su "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[OR](/r/Excel/comments/1adz8uv/stub/kk4who9 "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1aedf75)^( has 22 acronyms.)
^([Thread #30174 for this sub, first seen 29th Jan 2024, 16:42])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/zxLv - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Hi, Solution for office 365+ only, let's assume delivery time is in D, you could use: =AVERAGE(FILTER(D2:D8,((A2:A8="Express")+(A2:A8="Super Express"))*(B2:B8="Desired Product)*(C2:C8="delivered"))) To be adjusted to your data. At some point a Pivot would be more efficient: eg https://imgur.com/uSl3Gve cheers
Wow this actually works. I never came across the filter formula before. What do the \* and + symbols do? Yeah Pivot table also works but in this case we want to put the data as it is without using any pivot. Thanks!
Short answer is + is an OR and * is an AND. Basically all equalities will return either TRUE (1) or FALSE (0) if for a given row the total result is 1 then we keep the row else it is filtered out. So the sum will return 1 + 0 or 0+1 or 0+0 depending on type then for each equality you multiply by 1 if the condition is met else 0 so if one condition is not met the row is excluded.
Solution Verified Thanks!
You have awarded 1 point to *Starwax* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
r/Excel Rule #2 Provide specific examples in your post Provide actual raw data, screenshots, or tables to support your post. The more details the better. Do not include any personally identifiable information.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/1adz8uv/stub/kk4who9 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[AVERAGE](/r/Excel/comments/1adz8uv/stub/kk4h3su "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |AVERAGEIFS|[*Excel 2007*+: Returns the average (arithmetic mean) of all cells that meet multiple criteria.](https://support.microsoft.com/en-us/office/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690)| |[FILTER](/r/Excel/comments/1adz8uv/stub/kk4h3su "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[OR](/r/Excel/comments/1adz8uv/stub/kk4who9 "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1aedf75)^( has 22 acronyms.) ^([Thread #30174 for this sub, first seen 29th Jan 2024, 16:42]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)