Forum Discussion
Dem Apples
I have a large warehouse of apples. I am counting each apple by its color in every other cell. I need to ignore all data inbetween each cell, that data will not be counted. The list of apples will go on until the last column, XFD.
5 Replies
My guess is that you do not have Excel 365? Otherwise the main disruption to the formula I provided is not that the stride length between answers is now 5, it is that you want the results to be a horizontal list rather than vertical.
= LET( colorArr, TAKE(WRAPROWS(ColorRng, 5),, 1), matches, IF(colorArr={"Red","Green"}, 1), BYCOL(matches, Sumλ) )Exploiting 365 functionality allows one to select alternate values from the range 'colorRng' by wrapping the data to 2 rows and selecting the first.
= LET( colorArr, TAKE(WRAPCOLS(colorRng, 2), 1), matches, IF(colorArr={"Red";"Green"}, 1), BYROW(matches, Sumλ) ) where Sumλ = LAMBDA(x, SUM(x))Option 1 based on headers in Row 1
=countifs($1:$1,"<>", $3:$3, $A5)
Option 2 based on columns being ODD numbered
=SUMPRODUCT(MOD(COLUMN($3:$3),2)*($3:$3 =$A5))Thank you, I have changed my spreadsheet somewhat. How could I count the hundreds of apples by color using this sheet? I tried modifying your formula, mtarler, but I failed. Thank you.
- I don't know if you solved this but based on your image here are the modified formulas:
=countifs($1:$1,"<>", $21:$21, "Red")
=countifs($1:$1,"<>", $21:$21, "Green")
