Sumproduct formula copied

[rpinxt]

So I have this Sumproduct formula looking at sheet called ZHFM :

Please Login or Register  to view this content.

Now I also want to use this formula on another tab calles ZHFMLY. So I copied it and changed for all instances ZFHM to ZHFMLY :

Please Login or Register  to view this content.

But for some reason the new formula gives me an #N/A error, but there is data it should pick up.

Before I changed ZHFM to ZHFMLY the copied formula gave me data, but obviously from a different sheet.
But the data on the ZHFMLY sheet is good.

I am not familiar with this sumproduct formula so if anybody could explain to me what is going on I would be gratefull.

[Jonmo1]

The sheet name isn't the only thing that changed....

L$5 changed to F$5

[rpinxt]

Good eye Jonmo1. Unfortunately that is not the problem.
The formula was not copied from an equal position as the target cell.
But it is on the same line and F$5 is correct.

[Jonmo1]

Can you attach a copy of the file?

[rpinxt]

Well a sample.

Its the yellow cell.

ps: had to remove data and now #NA became #VALUE....

[Jonmo1]

In the book you posted, you missed one of the sheet names, look closely at the reference right after the INDEX function.

[rpinxt]

Correct Jonmo1.
But you can change that sheet name to ZHFMLY.
Annoying thing is it then works....
But I took data out of the tabs to make the workbook small enough.

If I copy this working formula to my real workbook and then edit the link to refer to the real workbook it goes into #NA again....

Any idea why that could be?

[rpinxt]

Ok...now I somehow have solved this, but I do not get it.....
In my examples of the formula above you see that I had a range of 2 - 10000.
The list of total data is always variable so I chose 10000.

Now I changed this to see what would happen to my formula which was not working anymore in ZHFMLY tab.
I changed the range to 1000 and that worked.... The formula gave no #NA anymore.

But more strange is the fact that the data on that sheet is more then 7400 lines long!!
However my range was only set to 1000 and still it summed the correct amount.

Now I can somehow explain this with the fact that the sumproduct must equal 2 variables set in the formula.
And when you look at how many lines belong to these variables/parametes then it are only 2 lines so well within 1000 range.

But still....why woks 10000 in sheet ZHFM and not in ZHFMLY, but 1000 will work and even sum the correct amounts even if the data on ZHFMLY is more then 7400 lines??

[rpinxt]

Ok sorry for making this a monologue

Found the problem now.
Wondering how far I could stretch the range which would work.
So from 1000 to 5000 to 6851....then I found that in line 6853 there was an #NA in the variables it had to chose from.....

Logically that the formula would then return an error.
And also I do now understand why it did work in my sample where i removed a lot of lines (including the one with the NA).