sumifs multiple columns help

Excel 2007- I cannot get my sumifs formula to work. If I just have the
sumrange one column of data it works perfect, but I need it to sum multiple
columns. Here is my current formula:
=SUMIFS('Original Data'!$C$2:$C$46668,'Original
Data'!$A$2:$A$46668,$A3,'Original Data'!$B$2:$B$46668,B$1)

I need the first part to read 'Original Data'!$C$2:$N$46668 so it goes from
column C to column N instead of just column C. But when I do this I get a
#Value error. It works just fine when I just have column C as the sumrange
but I need C thru N.

Hi,

Try this

=sumproduct(('Original Data'!$A$2:$A$46668=$A3)*('Original
Data'!$B$2:$B$46668=B$1)*('Original Data'!$C$2:$N$46668))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"duketter" wrote in message
...
Excel 2007- I cannot get my sumifs formula to work. If I just have the
sumrange one column of data it works perfect, but I need it to sum
multiple
columns. Here is my current formula:
=SUMIFS('Original Data'!$C$2:$C$46668,'Original
Data'!$A$2:$A$46668,$A3,'Original Data'!$B$2:$B$46668,B$1)

I need the first part to read 'Original Data'!$C$2:$N$46668 so it goes
from
column C to column N instead of just column C. But when I do this I get a
#Value error. It works just fine when I just have column C as the
sumrange
but I need C thru N.

Try

=SUMPRODUCT(('Original Data'!$A$2:$A$46668=$A3)*
('Original Data'!$B$2:$B$46668=B$1)*('Original Data'!$C$2:$N$46668))

--
Jacob (MVP - Excel)


"duketter" wrote:

Excel 2007- I cannot get my sumifs formula to work. If I just have the
sumrange one column of data it works perfect, but I need it to sum multiple
columns. Here is my current formula:
=SUMIFS('Original Data'!$C$2:$C$46668,'Original
Data'!$A$2:$A$46668,$A3,'Original Data'!$B$2:$B$46668,B$1)

I need the first part to read 'Original Data'!$C$2:$N$46668 so it goes from
column C to column N instead of just column C. But when I do this I get a
#Value error. It works just fine when I just have column C as the sumrange
but I need C thru N.