Sumif Question

[baggarwal]

Hi All:

I have the following table I need your help on.

mr excel question.xls
	A	B	C	D
3		Sales	Margin
4		2,100,000	60,000
5		2,000,000	50,000
6		1,900,000	40,000
7		1,800,000	30,000
8		1,700,000	20,000
9		1,600,000	18,000
10		1,500,000	15,000
11		1,400,000	20,000
12
13	>=2,000,000	4,100,000	110,000
14	>1,600,000but<2,000,000	5,400,000	90,000
Sheet1

Basically it is a table with sales and margin figures.

At the bottom I have a summary total for all values > 2,000,000 and >1,600,000 and<2,000,000.

I need a formula that will sum this automatically. I assume a sumif but I am not sure how to apply it. Right now I am sort decending and then summing manually.

The second thing I need is a formula that will total the margin for all sales> 2,000,000 and for >1,600,000 and<2,000,000 condition.

Can someone please provide me with the formulas that will automate this for me.

Thanks,

 

[Andrew Fergus]

Hi

Assuming cell A13 contains the value 2000000 and cell A14 contains the value 1600000, then use the following formula in cell B13, and copy it across to C13 :

=SUMIF($B4:$B11,">="&$A13,B4:B11)

Use the following formula in cell B14 and copy it across to cell C14 :

=SUMPRODUCT(B4:B11,--($B4:$B11>=$A14),--($B4:$B11<$A13))

HTH, Andrew

 

[baggarwal]

Can you please explain how the second formula works.

I have never seen -- added so I am not sure what it does in the formula.

 

[Andrew Fergus]

Hi

Aladin does a wonderful job of describing the sumproduct and double negative in this thread :

http://www.mrexcel.com/board2/viewtopic.php?t=229993

In a nutshell, the double negative is forcing the if test to be a value of 1 or 0 (instead of true or false) such that if both conditions are met you get the sum of the value * 1 * 1. If either or both of the conditions are not met the product would be either value * 1 * 0 or value * 0 * 1 or value * 0 * 0. The last three all equal zero such that they do not get added to the total.

HTH, Andrew