# SUMIF with two conditions??

#### CliveBoA

##### New Member
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Richard Schollar

##### MrExcel MVP
Hi & Welcome to the Board!

Say Hi to Sumproduct:

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

#### CliveBoA

##### New Member
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?

#### VoG

##### Legend
You've missed out some double minus signs:

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

#### CliveBoA

##### New Member
Thanks VoG II,

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

Regards,

Clive BoA

#### Richard Schollar

##### MrExcel MVP
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.

Replies
1
Views
647
Replies
5
Views
599
Replies
1
Views
330
Replies
1
Views
852
Replies
7
Views
427

1,191,204
Messages
5,985,262
Members
439,953
Latest member
suchitha

### 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