Forum Discussion
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, including the same words and numbers as are in the toy columns. In the example you see here, the words "Red" and "Blue" appear inbetween the toy columns, those words and columns should be not be included in the totals and averages. I am only interested in every other column. thank you.
- https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-search-for-multiple-records-for-the-same/15e19823-ca02-40dd-9736-e8234fc8bc3e
Try transfer to one dimension list first before calculation. =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.
=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.
- Thank you, this worked well. I have changed my workbook, the goal is the same, but the cells I use are different. I spent 20 minutes trying to adapt your formula to the new workbook,but I failed. Could you show me how to adapt it? I will post the new screenshot.
