Substitute for SUMIF

[Ophelia70]

Hello,

I have a workbook that is pulling data from another workbook with a SUMIF so if the workbook is closed there are just #value errors. Usually I would use a SUMPODUCT instead but I can’t make it work just for column Code and to pull a value back for column Q4 actual. Usually when I do a sumproduct it looks at two references to pull back a figure so maybe it just can’t be done for 1?

Any suggestions as to what else would work?

Code<o></o>	Description<o></o>		Q4 actual<o></o>​
800010<o></o>	Salaries<o></o>	<o></o>​	<o></o>​	11,000<o></o>​
800015<o></o>	Share Based Payments<o></o>	<o></o>​	<o></o>​	0<o></o>​
800016<o></o>	Employer's NI on SBP<o></o>	<o></o>​	<o></o>​	5,000<o></o>​
800020<o></o>	Directors Fees<o></o>	<o></o>​	<o></o>​	0<o></o>​
800030<o></o>	Employers National Insurance Contributions<o></o>	<o></o>​	<o></o>​	1,000<o></o>​
800040<o></o>	Employers Pension Contributions<o></o>	<o></o>​	<o></o>​	2,000<o></o>​
800100<o></o>	Temporary Staff<o></o>	<o></o>​	<o></o>​	31,845<o></o>​

<tbody> </tbody>

<o>

Thanks</o>

 

[Norie]

You can use SUMPRODUCT when there's only one column for criteria.

Can you post your existing SUMIF formula?

 

[Ophelia70]

This is the current formula

SUMIF('Other workbook'!$A:$A,A9,'Other workbook'!$J:$J)

 

[BarryL]

maybe:

=SUMPRODUCT(--('Other workbook'!$A:$A=A9),'Other workbook'!$J:$J)