SUMIF with two conditions??

CliveBoA

New Member
Joined
Aug 6, 2007
Messages
3
A MrExcel virgin hopes someone can help...

How can I create a SUMIF function with two conditions, reading from two different columns?

For example, I have a list of records that include columns for location, revenue and business line. I want to sum revenue for each business line in each location.
 

Excel Facts

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

Say Hi to Sumproduct:

=SUMPRODUCT(--(LocationRange="MyLocation"),--(BusinessLineRange="MyBusinessLine"),RevenueRange)

Note that:

1. You can't use whole column refs in Sumproduct (unless on xl2007) so no A:A but you can use A2:A1000.

2. The criteria (ie "MyBusinessLine", "MyLocation") can be cell references holding these values
 
Upvote 0
Thanks Richard,

I've put in the formula as shown below but my result is 0.

=SUMPRODUCT((C14:C60="Asia Pacific"),(I14:I60="Projects"),N14:N60)

Is this syntax correct?
 
Upvote 0
You've missed out some double minus signs:

Code:
=SUMPRODUCT(--(C14:C60="Asia Pacific"),--(I14:I60="Projects"),N14:N60)
 
Upvote 0
Thanks VoG II,

That is working fine now. What do the double minus signs indicate?

Regards,

Clive BoA
 
Upvote 0
The conditions within Sumproduct return an array or list of True/Falses depending on whether the criteria is True or False. The double minus coerces Trues to 1s and Falses to 0s so that these numerical equivalents can then be used to multiply against the other arrays or lists (ie A2:A100 for example). You will also see *1 and +0 used - they are all equiavlent. You may also see this structure used:

=SUMPRODUCT((C14:C60="Asia Pacific")*(I14:I60="Projects")*N14:N60)

which does exactly the same again.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top