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,
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,450
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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,450
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,899
Members
431,772
Latest member
dannyboi1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top