2D conditional calculation

Question

I have a spreadsheet with list of Products sold over time, with each product having it's class (I'd prefer if classes would be unlimited, but I can also live with predefined classes) and status active/inactive. I need an arrayformula for H5 to fill the yellow area (only active products). I've tried with combination of MMULT and Arrayformula, but no luck, maybe someone can help me out.

score 1 · Accepted Answer · answered Feb 18 '20 at 11:02

1

Class A:

=ARRAYFORMULA(MMULT(IF(($C2:$F2="Class A")*($C3:$F3=TRUE); 
 INDIRECT("C5:F"&COUNTA($A5:$A)+4)*1; 0); TRANSPOSE(SIGN($C5:$F5))))

Class B:

=ARRAYFORMULA(MMULT(IF(($C2:$F2="Class B")*($C3:$F3=TRUE); 
 INDIRECT("C5:F"&COUNTA($A5:$A)+4)*1; 0); TRANSPOSE(SIGN($C5:$F5))))

to turn it unlimited, remove F:

answered Feb 18 '20 at 11:02

player0

124,011
12
67
124

Thanx. I came up with a similar formula: =ArrayFormula(mmult(N(array_constrain($C$5:$G * ($C$3:$G$3=TRUE)*($C$2:$G$2="Class A") ;MATCH(2;1/($C$5:$C<>"");1);columns($C$5:$G$5)));sequence(columns($C$5:$G$5);1)^0)) – Mara Feb 18 '20 at 12:10

score 0 · Answer 2 · answered Feb 18 '20 at 09:35

0

You need

an array formula to apply the criteria to all rows
if statements to verify either the checkboxes are checked
to sum over several columns with the same class

This is how you can do it:

Type into H5

=ARRAYFORMULA(if(AND(C$3=true; C$2="Class A");C5:C8;0)+if(AND(D$3=true; D$2="Class A");D5:D8;0)+if(AND(E$3=true; E$2="Class A");E5:E8;0)+if(AND(F$3=true; F$2="Class A");F5:F8;0))

and into I5

=ARRAYFORMULA(if(AND(C$3=true; C$2="Class B");C5:C8;0)+if(AND(D$3=true; D$2="Class B");D5:D8;0)+if(AND(E$3=true; E$2="Class B");E5:E8;0)+if(AND(F$3=true; F$2="Class B");F5:F8;0))

answered Feb 18 '20 at 09:35

ziganotschka

25,866
2
16
33

Thanx, it works, but I forgot to mention I need unlimited columns and rows. That's why I put "..." in G1 and A9. – Mara Feb 18 '20 at 09:39
Unlimited rows is easy if you replace `C5:C8` by `C5:C` etc., but how do you want to implement unlimited columns if you want to sum over your classes in column H and I? – ziganotschka Feb 18 '20 at 09:48
I can live with a limited classes (Class A, Class B, Class C...), but I need to have unlimited number of products. Even if it is limited, it will be something like 30-40 columns. So I can limit the number of resulting columns, but the base columns should be unlimited. – Mara Feb 18 '20 at 09:50

Tom Sharpe · Answer 3 · 2020-02-18T21:45:54.013

Here's another approach which tries to address the 'infinite columns' issue. The difficulty with the question comes from the 2D nature of the data, so one approach is to normalise or reverse-pivot the data so that there is one row per data item. There are a number of ways of doing this see earlier question, but I have chosen to use the vlookup method:

=ArrayFormula({sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A)),
vlookup(roundup(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A))/counta(Sheet10!C3:3),0),{sequence(counta(Sheet10!A5:A)),filter(Sheet10!A5:A,Sheet10!A5:A<>"")},2,false),
hlookup(mod(sequence(counta(Sheet10!C2:2)*counta(Sheet10!A5:A),1,0),counta(Sheet10!C2:2)),{sequence(1,counta(Sheet10!C2:2),0);filter(Sheet10!C2:2,Sheet10!C2:2<>"")},2,false),
hlookup(mod(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A),1,0),counta(Sheet10!C3:3)),{sequence(1,counta(Sheet10!C3:3),0);filter(Sheet10!C3:3,Sheet10!C3:3<>"")},2,false),
vlookup(roundup(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A))/counta(Sheet10!C3:3),0),{sequence(counta(Sheet10!A5:A)),Sheet10!C5:index(Sheet10!C5:Z1000,counta(Sheet10!A5:A),counta(Sheet10!C3:3))},mod(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A),1,0),counta(Sheet10!C3:3))+2)
})

This gives you:

Once you have got this, it is fairly straightforward to wrap it in a pivot query to give you the required totals:

=ArrayFormula(query({sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A)),
vlookup(roundup(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A))/counta(Sheet10!C3:3),0),{sequence(counta(Sheet10!A5:A)),filter(Sheet10!A5:A,Sheet10!A5:A<>"")},2,false),
hlookup(mod(sequence(counta(Sheet10!C2:2)*counta(Sheet10!A5:A),1,0),counta(Sheet10!C2:2)),{sequence(1,counta(Sheet10!C2:2),0);filter(Sheet10!C2:2,Sheet10!C2:2<>"")},2,false),
hlookup(mod(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A),1,0),counta(Sheet10!C3:3)),{sequence(1,counta(Sheet10!C3:3),0);filter(Sheet10!C3:3,Sheet10!C3:3<>"")},2,false),
vlookup(roundup(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A))/counta(Sheet10!C3:3),0),{sequence(counta(Sheet10!A5:A)),Sheet10!C5:index(Sheet10!C5:Z1000,counta(Sheet10!A5:A),counta(Sheet10!C3:3))},mod(sequence(counta(Sheet10!C3:3)*counta(Sheet10!A5:A),1,0),counta(Sheet10!C3:3))+2)
},"select Col2,sum(Col5) where Col4=TRUE group by Col2 pivot Col3 label Col2 'Date' format Col2 'dd-mmm-yyyy'"))

which gives you

2D conditional calculation

3 Answers3