Prev: Graphs in Excel 2007
Next: Question using countif
From: duketter on 7 May 2010 16:15
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.
From: Ashish Mathur on 8 May 2010 03:20
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" <duketter(a)discussions.microsoft.com> wrote in message
news:AE7148F3-BA7A-482E-962F-3BDF2E623423(a)microsoft.com...
> 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.

From: Jacob Skaria on 8 May 2010 03:44
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.
 | 
Pages: 1
Prev: Graphs in Excel 2007
Next: Question using countif