So I have rows which are filled with an unknown number of entries, which are all composed of an ID, a name and an amount.

I have managed to sum every third cell starting on the seventh column using the following formula:

=SUMIF(ARRAYFORMULA(MOD((COLUMN(A6:6)-COLUMN(A6) 6),3)),0,A6:6)

Now I would like to find a way to only add cells where the corresponding ID contains an "INV" tag, but I have not been able to figure it out myself.

CodePudding user response：

• Easiest way is to just call

  =SUM(E2:2)
  

  This will ignore all text, but it's not specific to INV tags. Then you can simply drag fill down.

• To be specific for INV tags,

  =ARRAYFORMULA(MMULT(IF(REGEXMATCH(""&E2:M13,"^INV"),G2:M13,0),SEQUENCE(COLUMNS(E2:M13),1,1,)))
  

  • IF with offsetted/differential array arguments:
    • Condition array starting with E2
    • Result array starting with G2
  • REGEXMATCH to test for INV tag
  • MMULT to matrix multiply to get array sum
  • SEQUENCE to create a 1D ones array to provide a second argument to MMULT to get array sum

CodePudding user response：

Summing every third column if its adjacent column has substring

try:

=INDEX(MMULT(IF(FILTER(G2:13, MOD(COLUMN(G1:1)-2, 3)=0)="", 0, 
 FILTER(G2:13, MOD(COLUMN(G1:1)-1, 3)=0)), 
 SEQUENCE(COLUMNS(FILTER(G2:13, MOD(COLUMN(G1:1)-2, 3)=0)))^0))

for just INV tags use:

=INDEX(MMULT(IF(REGEXMATCH(FILTER(G2:13, MOD(COLUMN(G1:1)-2, 3)=0), "INV.*"), 
 FILTER(G2:13, MOD(COLUMN(G1:1)-1, 3)=0), 0), 
 SEQUENCE(COLUMNS(FILTER(G2:13, MOD(COLUMN(G1:1)-2, 3)=0)))^0))