SumIF not working for multiple column reference

[lorecarney]

I'm trying to create a coordinates formula (If that makes sense)
where a sum if, will give me the dollar amount of total spend by type of vendor
I have the months and the vendor names.
I used to have this CSE formula working perfectly in Excel 2003, but we upgraded to 2010 and not working right now.
BI Publisher sheet is where the information is.
in the formula below Column J refers to the month in the BI Publisher sheet (Example C4 1/31/2014)
column R to the vendor name (Example (B6 Vendor 1)
column M is the dollar amount spent
any other ways around this in v2010 ?

Example:

	Actual</SPAN>	Fsct</SPAN>	Fsct</SPAN>	Fsct</SPAN>	Fsct</SPAN>	Fsct</SPAN>	Fsct</SPAN>	Fsct</SPAN>	Fsct</SPAN>
ACTUAL SPEND</SPAN>	(C4)1/31/2014</SPAN>	Feb</SPAN>	Mar</SPAN>	Apr</SPAN>	May</SPAN>	Jun</SPAN>	Jul</SPAN>	Aug</SPAN>	Sep</SPAN>
Sales Promo
(B6)Vendor 1</SPAN>	{=sum(if((('BI Publisher'!$J$1:$J$14995)=$C$4)*(('BI Publisher'!$R$1:$R$14995)=B6,'BI Publisher'!$M$1:$M$14995))} </SPAN>
Vendor 2</SPAN>

<TBODY>
</TBODY>

 

[barry houdini]

This "array formula" should work

=SUM(IF('BI Publisher'!$J$1:$J$14995=$C$4,IF('BI Publisher'!$R$1:$R$14995=B6,'BI Publisher'!$M$1:$M$14995)))

...but in Excel 2010 it will be easier to use SUMIFS function which doesn't need "array entry", i.e.

=SUMIFS('BI Publisher'!$M$1:$M$14995,'BI Publisher'!$J$1:$J$14995,$C$4,'BI Publisher'!$R$1:$R$14995,B6)

As you can see, in SUMIFS the range to sum is the first range

 

[lorecarney]

Thank you!!! The sumIfs worked perfectly.