Sumifs

[David]

Hi,

I link sumifs function to another worksheet and whenever I recalculate the
sheet with the sumifs functions with the linked pages closed, I get the
#VALUE! error. Any idea how to avoid this error and have the function either
pull the data or keep what ever answers I had in the cell before it was
updated?

Thank you,

 

[T. Valko]

SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead.

 

[David]

I see where you're going with the sumproduct, but that's not exactly what i
need. I'm trying to match

 

[T. Valko]

Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do.

 

[David]

Sorry about that, i pressed post too early. What i'm trying to do is match a
specfic store in my company with a specific account and there are multiple
accounts in this list for each store.

 

[T. Valko]

Post the *exact* SUMIF/SUMIFS formula you are trying to use.

 

[David]

This is my current sumifs formula:

=SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE
DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2)

It currently works as is, but i'd like to see try this Sumproduct function
sounds like a better solution.

Thanks,

David

 

[T. Valko]

The SUMPRODUCT version would be:

=SUMPRODUCT(--('[4-204 1208.xls]JE DATA'!$B:$B=A5),--('[4-204 1208.xls]JE
DATA'!$C:$C=$F$2),'[4-204 1208.xls]JE DATA'!$E:$E)

Although you can use entire columns as range references with SUMPRODUCT in
Excel 2007 (only) I still wouldn't do it if you're not using all 1,000,000+
rows for data. I'd use the smallest specific range needed.

--
Biff
Microsoft Excel MVP

This is my current sumifs formula:

=SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE
DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2)

It currently works as is, but i'd like to see try this Sumproduct function
sounds like a better solution.

Thanks,

David

Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do.

 

[David]

That worked great! Thanks!

The SUMPRODUCT version would be:

=SUMPRODUCT(--('[4-204 1208.xls]JE DATA'!$B:$B=A5),--('[4-204 1208.xls]JE
DATA'!$C:$C=$F$2),'[4-204 1208.xls]JE DATA'!$E:$E)

Although you can use entire columns as range references with SUMPRODUCT in
Excel 2007 (only) I still wouldn't do it if you're not using all 1,000,000+
rows for data. I'd use the smallest specific range needed.

--
Biff
Microsoft Excel MVP

This is my current sumifs formula:

=SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE
DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2)

It currently works as is, but i'd like to see try this Sumproduct function
sounds like a better solution.

Thanks,

David

Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do.

--
Biff
Microsoft Excel MVP


I see where you're going with the sumproduct, but that's not exactly
what i
need. I'm trying to match

:

SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead.

--
Biff
Microsoft Excel MVP


Hi,

I link sumifs function to another worksheet and whenever I
recalculate
the
sheet with the sumifs functions with the linked pages closed, I get
the
#VALUE! error. Any idea how to avoid this error and have the
function
either
pull the data or keep what ever answers I had in the cell before it
was
updated?

Thank you,

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

That worked great! Thanks!

The SUMPRODUCT version would be:

=SUMPRODUCT(--('[4-204 1208.xls]JE DATA'!$B:$B=A5),--('[4-204 1208.xls]JE
DATA'!$C:$C=$F$2),'[4-204 1208.xls]JE DATA'!$E:$E)

Although you can use entire columns as range references with SUMPRODUCT
in
Excel 2007 (only) I still wouldn't do it if you're not using all
1,000,000+
rows for data. I'd use the smallest specific range needed.

--
Biff
Microsoft Excel MVP

This is my current sumifs formula:

=SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE
DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2)

It currently works as is, but i'd like to see try this Sumproduct
function
sounds like a better solution.

Thanks,

David

:

Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do.

--
Biff
Microsoft Excel MVP


I see where you're going with the sumproduct, but that's not exactly
what i
need. I'm trying to match

:

SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead.

--
Biff
Microsoft Excel MVP


Hi,

I link sumifs function to another worksheet and whenever I
recalculate
the
sheet with the sumifs functions with the linked pages closed, I
get
the
#VALUE! error. Any idea how to avoid this error and have the
function
either
pull the data or keep what ever answers I had in the cell before
it
was
updated?

Thank you,