Array formula with a constant?
 

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: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

Array formula with a constant?
 

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!

"Tester" wrote in message
...
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: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

Array formula with a constant?
 

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'!D1:D100=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" wrote in message
...
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: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

Array formula with a constant?
 

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" wrote in message
...
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'!D1:D100=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" wrote in message
...
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: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

Array formula with a constant?
 

You're welcome. Thanks for the feedback!

Biff

"Tester" wrote in message
...
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" wrote in message
...
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'!D1:D100=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" wrote in message
...
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: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