# SumIF not working for multiple column reference

#### lorecarney

##### New Member
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 Fsct Fsct Fsct Fsct Fsct Fsct Fsct Fsct ACTUAL SPEND (C4)1/31/2014 Feb Mar Apr May Jun Jul Aug Sep Sales Promo (B6)Vendor 1 {=sum(if((('BI Publisher'!\$J\$1:\$J\$14995)=\$C\$4)*(('BI Publisher'!\$R\$1:\$R\$14995)=B6,'BI Publisher'!\$M\$1:\$M\$14995))} Vendor 2

<TBODY>
</TBODY>

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

Thank you!!! The sumIfs worked perfectly.

Replies
2
Views
101
Replies
7
Views
1K
Replies
4
Views
173
Replies
1
Views
423
Replies
1
Views
198

1,196,514
Messages
6,015,649
Members
441,913
Latest member
Lhayden_69

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