Filter Table or Table Transformer - Sumproduct Formula

Question

Hello,

I have a large table with a "QTY" column and other columns identifying inclusion of the QTY (labeled as "A", "B", and "C" below).

I would like to create a "Total" row using the Sumproduct formula - add the QTY when the column contains an "X".

Thank you for your time!

Regards,

Emily

Katerina Kovriga {Stiltsoft} · Accepted Answer

Hi @Emily Berg ,

We can suggest using the Table Transformer macro for your case:

SELECT
"Total" as 'QTY',
SUM(IF(T1.'A' = "X", T1.'QTY', 0)) AS 'A',
SUM(IF(T1.'B' = "X", T1.'QTY', 0)) AS 'B',
SUM(IF(T1.'C' = "X", T1.'QTY', 0)) AS 'C'
FROM T1
UNION CORRESPONDING
SELECT * FROM T1

Hope this helps.