Hi
I need to sum a cell range (F2:F90) based on two conditions of two other
cell ranges (D2:D90="XXX" and I2:I90=""), where the parameter of the first
condition may change ("XXX" changes to "MFB", "TGDolfa", "GATV", etc.) and
the second remains constant (I2:I90="").
On one cell I tried the formula:
=SUMPRODUCT((D2:D90="MFB")*(I2:I90="")*(F2:F90))
and it worked as I got an accurate result.
Now the part that is driving me crazy...
When I copied the formula to the cell below and changed the first
condition's parameter to another valid value...
=SUMPRODUCT((D2:D90="TGDolfa")*(I2:I90="")*(F2:F90 ))
I get a result of ZERO when I can see data that should be added!
I tried changing the formula to
=SUMPRODUCT(--(D2:D90="TGDolfa"),--(I2:I90=""),(F2:F90))
without success.
I even tried to change the first condition's parameter in the first cell
(the one that works) and I get a result of zero!
The same happens when I change the * for the -- in the SUMPRODUCT syntax.
=SUMPRODUCT(--(D2:D90="MFB"),--(I2:I90=""),(F2:F90))
(thanks to the undo button I still keep that one working).

I know that SUMPRODUCT is the function I need but I have been able to make
it work in one cell only!
I tried to look for errors in the data but I know it is OK (no spurious
spaces, etc). If I try the formula for a single row
=SUMPRODUCT((D11="Boroondara")*(I11=""),F11) it works, but as soon as I
implement the cell range
=SUMPRODUCT((D2:D90="Boroondara")*(I2:I90="")*F2:F 90)
The result turns to zero.

Does someone know what I'm doing wrong?

Your formula should work
=SUMPRODUCT((D2:D90="TGDolfa")*(I2:I90="")*F2:F90)

Did you check whether I2:I90 range is actually blank "" . Try the below
version

=SUMPRODUCT((D2:D90="TGDolfa")*(TRIM(I2:I90)="")*F 2:F90)

If this post helps click Yes
---------------
Jacob Skaria


"Gusso007" wrote:

Hi
I need to sum a cell range (F2:F90) based on two conditions of two other
cell ranges (D2:D90="XXX" and I2:I90=""), where the parameter of the first
condition may change ("XXX" changes to "MFB", "TGDolfa", "GATV", etc.) and
the second remains constant (I2:I90="").
On one cell I tried the formula:
=SUMPRODUCT((D2:D90="MFB")*(I2:I90="")*(F2:F90))
and it worked as I got an accurate result.
Now the part that is driving me crazy...
When I copied the formula to the cell below and changed the first
condition's parameter to another valid value...
=SUMPRODUCT((D2:D90="TGDolfa")*(I2:I90="")*(F2:F90 ))
I get a result of ZERO when I can see data that should be added!
I tried changing the formula to
=SUMPRODUCT(--(D2:D90="TGDolfa"),--(I2:I90=""),(F2:F90))
without success.
I even tried to change the first condition's parameter in the first cell
(the one that works) and I get a result of zero!
The same happens when I change the * for the -- in the SUMPRODUCT syntax.
=SUMPRODUCT(--(D2:D90="MFB"),--(I2:I90=""),(F2:F90))
(thanks to the undo button I still keep that one working).

I know that SUMPRODUCT is the function I need but I have been able to make
it work in one cell only!
I tried to look for errors in the data but I know it is OK (no spurious
spaces, etc). If I try the formula for a single row
=SUMPRODUCT((D11="Boroondara")*(I11=""),F11) it works, but as soon as I
implement the cell range
=SUMPRODUCT((D2:D90="Boroondara")*(I2:I90="")*F2:F 90)
The result turns to zero.

Does someone know what I'm doing wrong?