Array formula with a constant?

[Tester]

Hi, I'm a bit rusty with this formula, which i have entered as an array and
have forgotten what i have done wrong;

My formula =IF('Purchase Ledger'!F:F=Creditors!B5,SUMIF('Purchase
Ledger'!D,Creditors!D3,'Purchase Ledger'!H:H),"")
has a problem with the first argument to check if the entries in column F of
the Purchase ledger is a match for the row in Creditors. I have created a
data list to ensure the spelling etc is correct so only identical names can
be displayed in the two spreadsheets. If there is a match then it checks the
month number and if that corresponds (Creditors!D3) then it totals all the
entries in the column that match.
How can i resolve the problem please?
TIA
Chris

 

[Tester]

Ok, fixed it. Changed colums to cell ranges and changed supplier names to
fixed numbers, then had to make sure column headers were not in the cell
ranges!

 

[Biff]

Hi!

You can't use entire columns as range arguments with array formulas (unless
you're using Excel 2007 beta).

=IF('Purchase Ledger'!F:F

Try this (normally entered, not an array):

=SUMPRODUCT(--('Purchase Ledger'!F1:F100=Creditors!B5),--('Purchase
Ledger'!D1100=Creditors!D3),'Purchase Ledger'!H1:H100)

You can't use entire columns as range arguments with Sumproduct (unless
you're using Excel 2007 beta)

Biff

 

[Tester]

Thanks Biff
I thought my changes had worked but found that the first record in the
Ledger was catching all the totals - your solution has worked and is much
easier.

Thanks again
Chris

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks Biff
I thought my changes had worked but found that the first record in the
Ledger was catching all the totals - your solution has worked and is much
easier.

Thanks again
Chris