Sumif / sumifs

Ebusua

New Member
Joined
Aug 17, 2014
Messages
22
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)

Thanks very much in advance
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:
Upvote 0
Thanks for your quick response Gaz_chops but the SUM formula returns a 0 (zero)! I did enter the Formula as an array.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top