SUMIF with two conditions??

[CliveBoA]

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.

 

[Richard Schollar]

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

 

[CliveBoA]

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]

You've missed out some double minus signs:

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

 

[CliveBoA]

Thanks VoG II,

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

Regards,

Clive BoA

 

[VoG]

The double minus signs are used to coerce expressions like

(C14:C60="Asia Pacific")

from True/False into numeric values which SUMPRODUCT can handle.

For an interesting discussion see http://www.mrexcel.com/board2/viewtopic.php?t=229993

 

[Richard Schollar]

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.