Forum Discussion
How do I repeat the same formula over multiple rows?
Each row in my table represents a separate grouping of cards, the numbers indicate which card is present in the grouping (e.g. 1= Card 1). I need to figure out how to check if two cards are present in the same group, and then repeat that formula across each row, to check how many rows/groups contain the same two cards. For example, in the screenshot I am checking if row/group 2 contains both 8 and 15. I can't figure out how to repeat this formula across each row in the data table to give me the total count of card pairings, in this case the total number of rows/groups where both 8 and 15 are present in the same row/group. Can anyone help me figure this out?
If you are using Excel for MS365 you can try this:
=SUM(--(BYROW(A2:K54,LAMBDA(r,--SUM(--(r={8;15}))>=2))))Perhaps not the most intuitive formula, but all it does is create, row-by-row, arrays of TRUEs and FALSEs. Then the -- turns these into ones and zeros that can be summed.
If you want to enter the Card parings in the worksheet, rater than hard-coding them in the formula enter then, let's say in N1 and N2 and than replace the {8;15} part with N1:N2
If you are not using 365, then I would use a helper column L. Enter your working formula in L2, copy it down and SUM it where you want the result to show up.
- Adding UNIQUE so that even if there is multiple 8 but no 15 in a given row the checks will still work: =SUM(--(BYROW(A5:K54,LAMBDA(r,--SUM(--(UNIQUE(r,TRUE,FALSE)={8;15}))>=2))))
If you are using Excel for MS365 you can try this:
=SUM(--(BYROW(A2:K54,LAMBDA(r,--SUM(--(r={8;15}))>=2))))Perhaps not the most intuitive formula, but all it does is create, row-by-row, arrays of TRUEs and FALSEs. Then the -- turns these into ones and zeros that can be summed.
If you want to enter the Card parings in the worksheet, rater than hard-coding them in the formula enter then, let's say in N1 and N2 and than replace the {8;15} part with N1:N2
If you are not using 365, then I would use a helper column L. Enter your working formula in L2, copy it down and SUM it where you want the result to show up.
Riny_van_Eekelen I'm pretty sure this worked. Thank you so much! I have been fighting with AI for two days over how to get this thing working.
