SUMPRODUCT not working?



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:
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...
I get a result of ZERO when I can see data that should be added!
I tried changing the formula to
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.
(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
The result turns to zero.

Does someone know what I'm doing wrong?



Jacob Skaria

Your formula should work

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


If this post helps click Yes

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question