Wondering if anyone could help me with these functions

When I use the sunif functions and offset functions on one workbook linking
to another workbook it normally works and would give me a value. However
after I saved the workbooks and closed them down and reopen the one that
contains the functions the formulas will give me #VALUE rather than a numeric
result until I open the workbook where the sumif data is linking to

How can I overcome this problem?

Please help

Thanks

Vivi

vivi wrote:
Wondering if anyone could help me with these functions

When I use the sunif functions and offset functions on one workbook
linking to another workbook it normally works and would give me a
value. However after I saved the workbooks and closed them down and
reopen the one that contains the functions the formulas will give me
#VALUE rather than a numeric result until I open the workbook where
the sumif data is linking to

How can I overcome this problem?


Hi Vivi,

It's normal behaviour of SUMIF function: it needs the linked file opened.

You can use, instead of SUMIF, the SUMPRODUCT function that doesn't have
this need.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thanks for your reply, but the sumproduct function cant help my problem
unfortunately as I am not mutiplying numbers.

For example

Book1: Cell A1 = Zoe, Cell A2=David, Cell A3=John, Cell A4=Zoe
Cell B1 = 20, Cell B2=10, Cell B3 = 30, Cell B4=25

Book2 Cell A1=David, Cell A2=Zoe and Cell A3=John
Then in Cell B1 to B3 a formula
=sumif('Book1'!A:A,'Book2'!A1,'Book1'!B:B)

Therefore Cell B1 should have the result of 10; Cell B2 is 45 and Cell B3 is
30

Therefore sumproduct does not help, is there another function that I can use?

Thanks a lot for ur help!!! :)



"Franz Verga" wrote:

vivi wrote:
Wondering if anyone could help me with these functions

When I use the sunif functions and offset functions on one workbook
linking to another workbook it normally works and would give me a
value. However after I saved the workbooks and closed them down and
reopen the one that contains the functions the formulas will give me
#VALUE rather than a numeric result until I open the workbook where
the sumif data is linking to

How can I overcome this problem?


Hi Vivi,

It's normal behaviour of SUMIF function: it needs the linked file opened.

You can use, instead of SUMIF, the SUMPRODUCT function that doesn't have
this need.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

vivi wrote:
Thanks for your reply, but the sumproduct function cant help my
problem unfortunately as I am not mutiplying numbers.

Sumproduct is useful not only to multiply numbers...


For example

Book1: Cell A1 = Zoe, Cell A2=David, Cell A3=John, Cell A4=Zoe
Cell B1 = 20, Cell B2=10, Cell B3 = 30, Cell B4=25

Book2 Cell A1=David, Cell A2=Zoe and Cell A3=John
Then in Cell B1 to B3 a formula
=sumif('Book1'!A:A,'Book2'!A1,'Book1'!B:B)

Therefore Cell B1 should have the result of 10; Cell B2 is 45 and
Cell B3 is 30

Therefore sumproduct does not help, is there another function that I
can use?



=SUMPRODUCT(('Book1'!A1:A30='Book2'!A1)*('Book1'!B 1:B30))

the only limitation to use of SUMPRODUCT is that you cannot use entire
columns/rows...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy