SUMIF across multiple worksheets in another workbook

[BOBODD]

I've tried reading other peoples' questions, but I can't seem to modify the
examples to fit my needs.

I have each month's commission payments from our clients on a seperate
worksheet (named January, February, March etc.) which is in another workbook
which may or may not be open. It's probably also worth noting that several of
the sheets are currently blank.

I want to be able create something similar to:

=SUMIF('[Workbook.xls]Januaryecember'!B:B,"Client ID
number",'[Workbook.xls]Januaryecember'!E:E)

where column B of each sheet contains each client's ID number & column E
shows how much they've paid.

Once I've managed this, I need to average the result over the number of
months completed so far (and hopefully have the formula update its result as
new data is entered in my source workbook.)

This feels like a big ask, but if anyone can help it would be greatly
appreciated.

 

[Franz Verga]

I've tried reading other peoples' questions, but I can't seem to
modify the examples to fit my needs.

I have each month's commission payments from our clients on a seperate
worksheet (named January, February, March etc.) which is in another
workbook which may or may not be open. It's probably also worth
noting that several of the sheets are currently blank.

I want to be able create something similar to:

=SUMIF('[Workbook.xls]Januaryecember'!B:B,"Client ID
number",'[Workbook.xls]Januaryecember'!E:E)

where column B of each sheet contains each client's ID number &
column E shows how much they've paid.

First of all you cannot use 3D references with SUMIF function. So you should
have a SUMIF for each month and than sum together.

The SUMIF function needs the second workbook (the one you are summing from)
to be opened, because if it's closed the function gives a #VALUE! error. You
can use, instead of SUMIF, the SUMPRODUCT function, remembering that you
cannot use whole columns inside this function and that you cannot use 3D
references, so your formula could be something like this:

=SUMPRODUCT(('[Workbook.xls]January'!B2:B1000="Client ID
number")*('[Workbook.xls]January'!E2:E1000))

then you have to replicate the formula for each month and then sum together.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[BOBODD]

Thanks.

I was afraid of that. I've currently got things set up where it sums each
month individually, but I was hoping for a more elegant solution.

I've tried reading other peoples' questions, but I can't seem to
modify the examples to fit my needs.

I have each month's commission payments from our clients on a seperate
worksheet (named January, February, March etc.) which is in another
workbook which may or may not be open. It's probably also worth
noting that several of the sheets are currently blank.

I want to be able create something similar to:

=SUMIF('[Workbook.xls]Januaryecember'!B:B,"Client ID
number",'[Workbook.xls]Januaryecember'!E:E)

where column B of each sheet contains each client's ID number &
column E shows how much they've paid.

First of all you cannot use 3D references with SUMIF function. So you should
have a SUMIF for each month and than sum together.

The SUMIF function needs the second workbook (the one you are summing from)
to be opened, because if it's closed the function gives a #VALUE! error. You
can use, instead of SUMIF, the SUMPRODUCT function, remembering that you
cannot use whole columns inside this function and that you cannot use 3D
references, so your formula could be something like this:

=SUMPRODUCT(('[Workbook.xls]January'!B2:B1000="Client ID
number")*('[Workbook.xls]January'!E2:E1000))

then you have to replicate the formula for each month and then sum together.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy