Associating different text cells to one text cell to calculate cost average

[Fruffball]

Hi,

i am struggling to figure out an Excel formula that allows me to group different text cells into one in order to calculate a grouped average.
In the attached file i have a table that shows Sales by Item and by Quarter.
The second, smaller table below needs to apply a formula that can calculate the average Sales for a group e.g. Toys, differentiated by the colors Orange and Green.

Whereas i can just calculate the average by choosing the Sales cells under each group, I hoped someone can help me figure out a more advanced formula by using 'averageifs' by ignoring 0 sales in conjunction to associating e.g. text cells Lego, Barbie,Games, Cards to the group name 'Toys'.
Thank you!

Attachment 782463

[Pepe Le Mokko]

There are instructions at the top of the page on how to post a sample sheet.Thanks
Your profile states XL 2203? Is this a new version?

[AliGW]

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version (NOT a release number like 2203) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[AliGW]

You cannot sum by cell colour with a formula. You will need to set up a lookup list somewhere in the workbook to categorise the individual items, and then reference this in the formula.

As already requested, please provide a sample workbook.

[Fruffball]

Thank you for all the comments and my apology as i am new to this and still learning. I have updated my version in my profile based on what i see on the program: Microsoft 365 MSO (Version 2203 Build 16.0.15028.20242) 64-bit. I also attached a file where i calculated average by selecting each relevant cell and using an automated formula that calculates averages by item. Yet, i tried to find a formula that helps me to associate different items to the group name item but i am not able to.

[Fruffball]

Thank you Ali, I corrected my version in the profile and attached a workbook.

[AliGW]

Try this:

=SUM(SUMIF($B$2:$B$25,$A$31:$A$34,$C$2:$C$25))/SUM(COUNTIFS($B$2:$B$25,$A$31:$A$34,$C$2:$C$25,"<>"))

and this:

=SUM(SUMIF($B$2:$B$25,$A$35:$A$38,$C$2:$C$25))/SUM(COUNTIFS($B$2:$B$25,$A$35:$A$38,$C$2:$C$25,"<>"))

[Fruffball]

Thank you so much, Ali. I tried it on my original data file but unfortunately it returns an Error stating 'Function divide parameter 2 cannot be 0'. To be frank, i dont quite know what that means.

[AliGW]

Nor do I - sorry.

If you can provide a sample workbook showing the issue, I'll take a look. This is NOT an error message that I have ever seen on Excel!