# Sumif / sumifs

#### Ebusua

##### New Member
Good morning

I have a SUMIFS formula from 2010 (below) which I need to use in Excel 2003, any ideas?

=SUMIFS('Source Data'!N:N,'Source Data'!G:G,C4,'Source Data'!C:C,B4)

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

=SUM(IF('Source Data'!G:G=C4,if('Source Data'!C:C=B4,'Source Data'!N:N))

Enter as an array (Ctrl, Shift & Enter)

Last edited:
Thanks for your quick response Gaz_chops but the SUM formula returns a 0 (zero)! I did enter the Formula as an array.

You cannot use entire column references in an array (or Sumproduct) formula in 2003, so:

=SUMPRODUCT('Source Data'!\$N\$2:\$N\$1000,('Source Data'!\$G\$2:\$G\$1000=C4)*('Source Data'!\$C\$2:\$C\$1000=B4))
for example.

Apologies, Thanks Rory I forgot about that.

Thanks for your quick response Gaz_chops but the SUM formula returns a 0 (zero)! I did enter the Formula as an array.

Excel 2003 does not admit whole column references in array-processing formulas. Try to use definite references...

=SUM(IF('Source Data'!\$G\$2:\$G\$400=C4,IF('Source Data'!\$C\$2:\$C\$400=B4,'Source Data'!\$N\$2:\$N\$400)))

If needed, you can switch to dynamic named ranges.

Thank you very much for all your quick responses

You're welcome, I was nearly right!

Replies
16
Views
747
Replies
3
Views
1K
Replies
8
Views
259
Replies
6
Views
568
Replies
12
Views
341

1,196,254
Messages
6,014,273
Members
441,810
Latest member
LouLou1234

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