=SUMPRODUCT ..... Help Please

[MarkAn]

Hi there

SUMPRODUCT is giving me a headache as I can't figure out where I am going wrong.

I have a spreadsheet with data in Columns A to W, Column E I have Named as TSStatus and Column U has been Named TSCom.

TSStatus has a range E2:E20000 and holds information "Not Started", "Pending", "Closed"
TSCom has a range U2:U20000 and holds information "SP" or "PC"

I have entered the following formula:

=SUMPRODUCT(--(TSStatus=$B$15),--(TSCom=$B$16))

With B15 holding Not Started and B16 holding SP

However, it continually brings back an error, however, it has calcuated on a previous tab using Named ranges.

Can anyone please help?

Many thanks
MarkAn

 

[Domenic]

Which error are you geting? If you're getting #NAME? as the error, then it's likely that the scope of your named ranges are limited to the worksheet. If so, let's say that the named ranges are located in Sheet2, try...

=SUMPRODUCT(--('Sheet2'!TSStatus=$B$15),--('Sheet2'!TSCom=$B$16))

 

[MarkAn]

Hi

Thanks for the help, I have tried to reference in the Worksheet, however, it reverts to the Workbook name.

I get the #NAME? error, or #N/A error

I am at a lose of what to do, as I have used the same formula in a different cell, referencing to another Worksheet (and different Named ranges) and it works ok.

 

[Domenic]

Can you post the exact reference you have in the "Refers to" box for each name?