SUMIF returned #VALUE! when I Move or Copy into new book

Hi all,

I was applying SUMIF formulae in Book A Sheet 1, and then when I move or copy the Book A Sheet 1 and paste into Book B Sheet 1, all the formulae returned #VALUE!
I understood that this SUMIF is an array formula, so I've hit ctrl-shift-enter instead of enter in Book A Sheet 1 before I move or copy.
However by move and copy paste into new Sheet, it's all returned in #VALUE!

I do not want to open the resources file every time when I move the sheets from A to B as the resources file are huge.
It's just not one resources file that I have to open, it's 12 BIG resources file !!

Someone please advice me some alternative solution without open the resources file....

It has to use sumif instead of vlookup because of the shorter formulae involved.

Thanks in advance
cks1026.

Hi Special-K,

How can I do so using Sumproduct? both are not working the same, or how can it work the same as SUMIF?

Thanks
cks1026

Hi Special-K,

Something like this

=SUMIF('[1st Sept AP-ALL DIV MLE Report amended.xlsx]MLE in $ value'!$A$4:$A$53,$A$165,'[1st Sept AP-ALL DIV MLE Report amended.xlsx]MLE in $ value'!D$4:D$53)/1000+SUMIF('[1st Sept AP-ALL DIV MLE Report amended.xlsx]MLE in $ value'!$A$4:$A$53,$A$165,'[1st Sept AP-ALL DIV MLE Report amended.xlsx]MLE in $ value'!S$4:S$53)

Basically it means, forecast in v$ + (rebates in $)
example, $3.5mil + (-$0.5mil) = $3mil

thx in advance

Hi Special-K, and daddylonglegs,

Thanks for responding...

For some reason both set of formulae return in #VALUE!...
Maybe I miss out something here, but then, I can tell you column A is not numeric, it's more like a description
Column D and Column S are numeric....

Thanks for looking into my problem....

Regards
cks1026