Sumif Question

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
Hi All:

I have the following table I need your help on.
mr excel question.xls
ABCD
3SalesMargin
42,100,00060,000
52,000,00050,000
61,900,00040,000
71,800,00030,000
81,700,00020,000
91,600,00018,000
101,500,00015,000
111,400,00020,000
12
13>=2,000,0004,100,000110,000
14>1,600,000but<2,000,0005,400,00090,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,
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

Well-known Member
Joined
Jul 10, 2002
Messages
591
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,884
Messages
5,543,023
Members
410,583
Latest member
gazz57
Top