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

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

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?

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.

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.

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

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,"<>"))

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!