SUMIFS to SUMPRODUCT

**plsm5882** · 01-31-2013, 05:09 PM

Hi, I need this formula converted into an Excel 2003 friendly formula: SUMIFS(Income!$P:$P,Income!$E:$E,$Q$6:$Q$70,Income!$C:$C,$R$5)

I assume SUMPRODUCT will do it? Something like: SUMPRODUCT((Income!$P:$P)*(Income!$E:$E=$Q$6:$Q$70)*(Income!$C:$C=$R$5))

But that SUMPRODUCT gives me a #VALUE!.

Income!$P:$P = column of amounts
Income!$E:$E = column of peoples names
$Q$6:$Q$70 = column of peoples names
Income!$C:$C = column of status (Invoiced or Paid)
$R$5 = Paid

I would appreciate a little help please.

Thanks

Peter

**oeldere** · 01-31-2013, 05:12 PM

Please Login or Register  to view this content.

The range of all data need to be the same.

e.g.

Please Login or Register  to view this content.

See that i added 20 and 200 in the reange.

Tip: You better not use the whole column as range.

**Ace_XL** · 01-31-2013, 05:15 PM

Restrict range to exclude non-data and do not specify entire columns as range. Also ensure, the ranges are consistent across columns

**plsm5882** · 01-31-2013, 05:17 PM

Thanks for your response.

I need to use the whole column as range and your second argument (Income!$E20:$E200=$Q$6) doesn't do what I need unfortunately. I need to look at a whole column and see where in another whole column names match, then get the amounts where the names match and add them up.

Maybe SUMPRODUCT is not the right formula?