9 Replies Latest reply: Sep 10, 2014 11:50 AM by Massimo Grossi

# Sumif and Rangesum Question

Class

YearAmount
Turnover20141000
Turnover2013800
COGS2014250
COGS2013326
Finance costs20143.5
Finance costs201311
Operating expenses201412
Operating expenses201340

The above data is from my table. Would anyone teach me some simple techniques?

1. Sumif condition (Year = 2014, Class=Turnvover and COGS)

I have tried: sum(if(Class='Turnover' and Class='COGS',Amount)) but it is not work

2. Answer for 2013 Gross Profit

I have tried: Rangesum(Class='Turnover', Amount) - Rangesum(Class='COGS', Amount)

Can anyone correct my mistake, many thanks!

• ###### Re: Sumif and Rangesum Question

For the first question expression is

=Sum(if(Year=2014 AND (Class='COGS' OR Class='Turnover'), Amount))

And for the second:

= Sum(if(Class='Turnover', Amount)) - Sum(if(Class='COGS', Amount))

You can use also Set Analysis

• ###### Re: Sumif and Rangesum Question

it says error in expression

• ###### Re: Sumif and Rangesum Question

=Sum(if(Year=2014 AND (Class='COGS' OR Class='Turnover'), Amount, 0))

= Sum(if(Class='Turnover', Amount,0)) - Sum(if(Class='COGS', Amount, 0))

• ###### Re: Sumif and Rangesum Question

See solution from Massimo, is more efficient with Set Analysis

• ###### Re: Sumif and Rangesum Question

Thanks again.

• ###### Re: Sumif and Rangesum Question

1)

for max year

sum({\$<Year={\$(=max(Year))},Class={Turnover,COGS}>} Amount)

for 2014

sum({\$<Year={2014},Class={Turnover,COGS}>} Amount)

2)

for max year

sum({\$<Year={\$(=max(Year))},Class={Turnover}>} Amount)

-

sum({\$<Year={\$(=max(Year))},Class={COGS}>} Amount)

for 2013 replace with 2013

• ###### Re: Sumif and Rangesum Question

Thank you very much for your help. I can solve it.

• ###### Re: Sumif and Rangesum Question

Set analysis answer by Massimo is better to use and easy to understand. You can update or modify it in future , in case your requirement changes. Attached is qvw for this.

• ###### Re: Sumif and Rangesum Question

When you think you get the answer, please close this discussion by giving Correct and Helpful answers to the posts which are useful for you.  It helps others in finding answers for similar scenarios. Thanks and good Qlik.