# Sumif Question

#### baggarwal

##### Well-known Member
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,

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

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.

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

Replies
1
Views
70
Replies
4
Views
227
Replies
4
Views
91
Replies
4
Views
185
Replies
7
Views
164

1,203,071
Messages
6,053,369
Members
444,658
Latest member
lhollingsworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back