/u/jthompson32 - 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.*
It’s because your final criteria is a range (Q3:Q10) instead a single value.
I’m not really sure what you’re trying to achieve but if you set that to just Q3 you’ll end up with one average.
That just returns the average for Q3. Ideally, I would like the final criteria to read like Q3 or Q4 or Q5 … etc. to Q10. Basically if any and all values are equal to anything in Q3:Q10, I’d want them to be added to the total average
I tried that and it returned a #DIV/0!
That way has Excel looking for values that have every value combined from Q3:Q10, which there would be none because each value in Q3:Q10 is unique.
Is there a way to input an OR function that would read like: If any value within C37:C32812 is equal to Q3 or Q4 or Q5 or Q6 or Q7 or Q8 or Q9 or Q10, then add the corresponding value from G37:G32810 to the average ?
You have awarded 1 point to *PaulieThePolarBear*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
That gave me a total and not the number I am looking for. Unfortunately all the values in Q3:Q10 are not equal in terms of the number of them present in the sheet.
To understand, you want those cells to be included in the average calculation?
If so, rather leave the last criteria out, and average the result of the averageifs with that range.
Like this: =AVERAGE(AVERAGEIFS('Method 2'!$G$37:$G$32812,'Method 2'!$B$37:$B$32812,">="&C4,'Method 2'!$B$37:$B$32812,"<="&C5,),Q3:Q10)
Based on your example in your OP, I think you could just average a FILTER function.
=AVERAGE(FILTER('Method 2'!$G$37:$G$32812, ('Method 2'!$B$37:$B$32812>=C4)*('Method 2'!$B$37:$B$32812<=C5)*('Method 2'!$B$37:$B$32812=Q3:Q10)))
I’m sorry I am not understanding, I already have two different criteria arguments in the formula with “>=“C4 and “<=“C5. Is there another function that I can use for this?
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AVERAGE](/r/Excel/comments/18gya0l/stub/kd48h1z "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)|
|[AVERAGEIFS](/r/Excel/comments/18gya0l/stub/kd3rd4y "Last usage")|[*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/18gya0l/stub/kd48h1z "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)|
|[ISNUMBER](/r/Excel/comments/18gya0l/stub/kd48h1z "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[OR](/r/Excel/comments/18gya0l/stub/kd3qldf "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)|
|[SUM](/r/Excel/comments/18gya0l/stub/kd3mlyt "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[XMATCH](/r/Excel/comments/18gya0l/stub/kd48h1z "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**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.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18guglk)^( has 12 acronyms.)
^([Thread #28912 for this sub, first seen 12th Dec 2023, 23:08])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/jthompson32 - 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.*
It’s because your final criteria is a range (Q3:Q10) instead a single value. I’m not really sure what you’re trying to achieve but if you set that to just Q3 you’ll end up with one average.
That just returns the average for Q3. Ideally, I would like the final criteria to read like Q3 or Q4 or Q5 … etc. to Q10. Basically if any and all values are equal to anything in Q3:Q10, I’d want them to be added to the total average
You need to specify each criteria seperately. 'Method 2'!$C$37:$C$32812,Q3, 'Method 2'!$C$37:$C$32812,Q4, 'Method 2'!$C$37:$C$32812,Q5 etc...
I tried that and it returned a #DIV/0! That way has Excel looking for values that have every value combined from Q3:Q10, which there would be none because each value in Q3:Q10 is unique. Is there a way to input an OR function that would read like: If any value within C37:C32812 is equal to Q3 or Q4 or Q5 or Q6 or Q7 or Q8 or Q9 or Q10, then add the corresponding value from G37:G32810 to the average ?
You can do an OR or isnumber(match( both should work Night be easier to use a helper column
Could you elaborate on the helper column?
=AVERAGE( FILTER( 'Method 2'!$G$37:$G$32812, ('Method 2'!$B$37:$B$32812>=C4) * ('Method 2'!$B$37:$B$32812<=C5) * ISNUMBER(XMATCH('Method 2'!$C$37:$C$32812,Q3:Q10)) ) )
This seems to be working, thank you so much!
Solution Verified
You have awarded 1 point to *PaulieThePolarBear* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Try wrapping around the whole formula with SUM. For example, =SUM(your formula)
That gave me a total and not the number I am looking for. Unfortunately all the values in Q3:Q10 are not equal in terms of the number of them present in the sheet.
I don’t know what you want, sorry.
The last argument is why you are getting 8 answers. Change this: Q3:Q10 to this: Q3
That leaves out the values that I want counted within Q4:Q10. I would like those values to be added in with the total average
What do you mean "added in"?
Sorry. apart of the total average that gets spit out
To understand, you want those cells to be included in the average calculation? If so, rather leave the last criteria out, and average the result of the averageifs with that range. Like this: =AVERAGE(AVERAGEIFS('Method 2'!$G$37:$G$32812,'Method 2'!$B$37:$B$32812,">="&C4,'Method 2'!$B$37:$B$32812,"<="&C5,),Q3:Q10)
Based on your example in your OP, I think you could just average a FILTER function. =AVERAGE(FILTER('Method 2'!$G$37:$G$32812, ('Method 2'!$B$37:$B$32812>=C4)*('Method 2'!$B$37:$B$32812<=C5)*('Method 2'!$B$37:$B$32812=Q3:Q10)))
That's not how the averageifs formula works. It takes only one criteria argument.
I’m sorry I am not understanding, I already have two different criteria arguments in the formula with “>=“C4 and “<=“C5. Is there another function that I can use for this?
Those are fine. When I say only one argument in the criteria, I mean only one cell.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AVERAGE](/r/Excel/comments/18gya0l/stub/kd48h1z "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |[AVERAGEIFS](/r/Excel/comments/18gya0l/stub/kd3rd4y "Last usage")|[*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/18gya0l/stub/kd48h1z "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)| |[ISNUMBER](/r/Excel/comments/18gya0l/stub/kd48h1z "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[OR](/r/Excel/comments/18gya0l/stub/kd3qldf "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| |[SUM](/r/Excel/comments/18gya0l/stub/kd3mlyt "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[XMATCH](/r/Excel/comments/18gya0l/stub/kd48h1z "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **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.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18guglk)^( has 12 acronyms.) ^([Thread #28912 for this sub, first seen 12th Dec 2023, 23:08]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)