Error with SUMIFS

[drewberts]

I have 2 separate workbooks. WB1 contains a list of invoices (Inv No in Col A, Job No in Col B & Amount in Col C) in a Table called "TabInvoices" on a sheet called "Invoices". There are sometimes more than one invoice per job.

e.g

Inv No Job No Amount
100 1250 £500
101 1261 £600
102 1245 £100
103 1250 £250

In the above dataset Job No 1250 has 2 invoices totalling £750 (Inv 100 & 103).

In my second workbook I want to type in a job No in Col A and the TOTAL of all invoices for that job number calculate in Col B. So in this example...

Job No Total
1250 750

I'm using SUMIFS as follows:-

=SUMIFS([WB1.xlsm]Invoices!$A$2:$C$1000,WB1.xlsm!TabInvoices[Job No],$A2)

...and I'm getting an error. I'm assuming it has to do with the syntax or am I using the wrong function? Both workbooks are open.

What is the obvious problem I'm creating for myself?

 

[JamesCanale]

Two things to consider - you are summing columns A, B, and C. I guess you only want to sum C. That array is 999 rows.

[Job No] is some number of rows. But if it isn't 999, it will throw an error at you. - and no brackets around your file name on the second one.

 

[drewberts]

Two things to consider - you are summing columns A, B, and C. I guess you only want to sum C. That array is 999 rows.

[Job No] is some number of rows. But if it isn't 999, it will throw an error at you. - and no brackets around your file name on the second one.

Many thanks James

I've gone with this and it seems to work...

=SUMIFS([WB1.xlsm]Invoices!$C$1:$C$1000,[WB1.xlsm]Invoices!$B$1:$B$1000,$A2)