Sumis funchtion and offset function
 

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

Sumis funchtion and offset function
 

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

Sumis funchtion and offset function
 

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

Sumis funchtion and offset function
 

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