Running into "multi-cell array formulas are not allowed in tables" error - what now?

[keelaboosa]

I'm trying to build the MASK column such that a 1 is displayed for the first distinct COMPOUND that has VOL * FLOW > 0, but I can't get it to work in the table using SUM(IF(FREQUENCY constructs; I get a "multi-cell array formulas are not allowed in tables" error.

What other options would I have to build this? I'd like to keep it a formula-based solution rather than VBA so people can follow the logic. I keep the VBA locked and hidden.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]COMPOUND
[/TD]
[TD]VOLUME
[/TD]
[TD]FLOW
[/TD]
[TD]MASK
[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

40​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

F​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

A​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

B​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

C​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

D​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

E​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

G​

[/TD]
[TD]

135​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

A​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

H​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

D​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

E​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Or perhaps:

=IF([@VOLUME]*[@FLOW],--(1=SUMPRODUCT(--(INDEX([COMPOUND],1):[@COMPOUND]=[@COMPOUND]),--(INDEX([VOLUME],1):[@VOLUME]<>0),--(INDEX([FLOW],1):[@FLOW]<>0))),0)