Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Filter Table or Table Transformer - Sumproduct Formula

Emily Berg
Contributor
May 17, 2022

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!

desired_output.png

Regards,

Emily

1 answer

1 accepted

3 votes
Answer accepted
Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 18, 2022

Hi @Emily Berg ,

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

Wed 14-3.png

Wed 14-1.png

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

Wed 14-2.png

Hope this helps.

Emily Berg
Contributor
June 9, 2022

@Katerina Kovriga _Stiltsoft_  - this is awesome, thank you!!

Emily

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events