ExcelBanter - SUMPRODUCT returns #VALUE error

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - SUMPRODUCT returns #VALUE error (https://www.excelbanter.com/excel-discussion-misc-queries/179428-sumproduct-returns-value-error.html)

John

SUMPRODUCT returns #VALUE error

Similar to the recently posted item I have a problem with a SUMPRODUCT
returning a #VALUE error

The formula is
=SUMPRODUCT(--('Forecast Data'!A6:C61=A6),--('Forecast Data'!N6:O61='Client
Forecast'!B9),'Forecast Data'!Q6:Q61)

I think the problem lies in the fact that 'Forecast Data'!A6:C61 - the
colums have merge cells in them - so Forecast Data'!A6-B6-C6 have been merged
into one. The value in that merged cell is what the formula is trying to
compare with the single cell - A6

Hope this makes sense and someone can help.

John

PCLIVE

SUMPRODUCT returns #VALUE error

Just use the single column reference.

=SUMPRODUCT(--('Forecast Data'!A6:A61=A6),--('Forecast Data'!N6:N61='Client
Forecast'!B9),'Forecast Data'!Q6:Q61)

HTH,
Paul

--

"John" wrote in message
...
Similar to the recently posted item I have a problem with a SUMPRODUCT
returning a #VALUE error

The formula is
=SUMPRODUCT(--('Forecast Data'!A6:C61=A6),--('Forecast
Data'!N6:O61='Client
Forecast'!B9),'Forecast Data'!Q6:Q61)

I think the problem lies in the fact that 'Forecast Data'!A6:C61 - the
colums have merge cells in them - so Forecast Data'!A6-B6-C6 have been
merged
into one. The value in that merged cell is what the formula is trying to
compare with the single cell - A6

Hope this makes sense and someone can help.

John

John

SUMPRODUCT returns #VALUE error

Thanks Paul - done the trick.

Cheers

John

"PCLIVE" wrote:

Just use the single column reference.

=SUMPRODUCT(--('Forecast Data'!A6:A61=A6),--('Forecast Data'!N6:N61='Client
Forecast'!B9),'Forecast Data'!Q6:Q61)

HTH,
Paul

--

"John" wrote in message
...
Similar to the recently posted item I have a problem with a SUMPRODUCT
returning a #VALUE error

The formula is
=SUMPRODUCT(--('Forecast Data'!A6:C61=A6),--('Forecast
Data'!N6:O61='Client
Forecast'!B9),'Forecast Data'!Q6:Q61)

I think the problem lies in the fact that 'Forecast Data'!A6:C61 - the
colums have merge cells in them - so Forecast Data'!A6-B6-C6 have been
merged
into one. The value in that merged cell is what the formula is trying to
compare with the single cell - A6

Hope this makes sense and someone can help.

John

All times are GMT +1. The time now is 08:47 PM.