Forum Discussion

User537843's avatar
User537843
Copper Contributor
Jun 06, 2023

Column skipping

I have 100 toys, and I would like to get the total profit for each color of toy, and the average profit. 1 column for each toy, 100 columns. In between each toy column will be random various text, in...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 06, 2023

User537843 

=SUM(IF(($A$2:$GS$2="red")*(MOD(COLUMN(A:GS),2)=0),$A$3:$GS$3))
=AVERAGE(IF(($A$2:$GS$2="red")*(MOD(COLUMN(A:GS),2)=0),$A$3:$GS$3))

You can try these formulas. Enter the formulas with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. For blue you can replace "red" by "blue" in the formula.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 07, 2023

    User537843 

    =AVERAGE(IF((MOD(COLUMN(T:SU),5)=0)*(Q21:SR21="red"),T3:SU3))
    =SUM(IF((MOD(COLUMN(T:SU),5)=0)*(Q21:SR21="red"),T3:SU3))

    You can try these formulas. Enter the formulas with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. For blue you can replace "red" by "blue" in the formula.

     

Resources