Hi

I have a Summary tab in a worksheet that does a calculation, and that
calculation changes based on the month that is selected from a drop-down in a
different tab. Which is to say, when I select July, the calculation in the
Summary tab displays one set of values. When I select August, the values in
the same set of cells change.

I am using the SUMPRODUCT formula to add up the values in the calculation
tab based on 2 criteria. When I initially set up the formula using July, it
was working well and picking out the correct values to add up. But when I
used the drop-down feature to select August, the values in my Summary tab
changed (as they should), but the SUMPRODUCT formula was still outputting the
same values from July.

Any ideas why this would be happening?

Thanks!

And by the way, the SUMPRODUCT formula simply refers to the Summary tab and
does not have anything to do with which month is selected...

"lkd" wrote:

Hi

I have a Summary tab in a worksheet that does a calculation, and that
calculation changes based on the month that is selected from a drop-down in a
different tab. Which is to say, when I select July, the calculation in the
Summary tab displays one set of values. When I select August, the values in
the same set of cells change.

I am using the SUMPRODUCT formula to add up the values in the calculation
tab based on 2 criteria. When I initially set up the formula using July, it
was working well and picking out the correct values to add up. But when I
used the drop-down feature to select August, the values in my Summary tab
changed (as they should), but the SUMPRODUCT formula was still outputting the
same values from July.

Any ideas why this would be happening?

Thanks!

i had this issue and the solution is not intuitive. you need to use dashes
this way you do not even need to have the source file open
here is how
=SUMPRODUCT(--(A1:A10=1),--(B1:B10=2),--(C1:C10=3),(D1:D10))


"lkd" wrote:

And by the way, the SUMPRODUCT formula simply refers to the Summary tab and
does not have anything to do with which month is selected...

"lkd" wrote:

Hi

I have a Summary tab in a worksheet that does a calculation, and that
calculation changes based on the month that is selected from a drop-down in a
different tab. Which is to say, when I select July, the calculation in the
Summary tab displays one set of values. When I select August, the values in
the same set of cells change.

I am using the SUMPRODUCT formula to add up the values in the calculation
tab based on 2 criteria. When I initially set up the formula using July, it
was working well and picking out the correct values to add up. But when I
used the drop-down feature to select August, the values in my Summary tab
changed (as they should), but the SUMPRODUCT formula was still outputting the
same values from July.

Any ideas why this would be happening?

Thanks!

This is actually the format that my formula is in:
=SUMPRODUCT(--(Summary!A:A=A13),--(Summary!D:D0),Summary!C:C)

The information that it is pulling in is from the same workbook, but a
different tab. You mentioned that the source workbook doesn't need to be
open for SUMPRODUCT, so maybe this is part of the problem? Does SUMPRODUCT
store the information that it initially processes? Can it not recognize when
the data in the cells in references has changed?

"igorek" wrote:

i had this issue and the solution is not intuitive. you need to use dashes
this way you do not even need to have the source file open
here is how
=SUMPRODUCT(--(A1:A10=1),--(B1:B10=2),--(C1:C10=3),(D1:D10))


"lkd" wrote:

And by the way, the SUMPRODUCT formula simply refers to the Summary tab and
does not have anything to do with which month is selected...

"lkd" wrote:

Hi

I have a Summary tab in a worksheet that does a calculation, and that
calculation changes based on the month that is selected from a drop-down in a
different tab. Which is to say, when I select July, the calculation in the
Summary tab displays one set of values. When I select August, the values in
the same set of cells change.

I am using the SUMPRODUCT formula to add up the values in the calculation
tab based on 2 criteria. When I initially set up the formula using July, it
was working well and picking out the correct values to add up. But when I
used the drop-down feature to select August, the values in my Summary tab
changed (as they should), but the SUMPRODUCT formula was still outputting the
same values from July.

Any ideas why this would be happening?

Thanks!