Array formula with a constant?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tester - 31 Oct 2006 22:17 GMT

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

Reply to this Message

Tester - 31 Oct 2006 23:06 GMT

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!

> Hi, I'm a bit rusty with this formula, which i have entered as an array
> and have forgotten what i have done wrong;
[quoted text clipped - 10 lines]
> TIA
> Chris

Reply to this Message

Biff - 31 Oct 2006 23:10 GMT

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

> Hi, I'm a bit rusty with this formula, which i have entered as an array
> and have forgotten what i have done wrong;
[quoted text clipped - 10 lines]
> TIA
> Chris

Reply to this Message

Tester - 31 Oct 2006 23:32 GMT

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

> Hi!
>
[quoted text clipped - 27 lines]
>> TIA
>> Chris

Reply to this Message

Biff - 31 Oct 2006 23:44 GMT

You're welcome. Thanks for the feedback!

Biff

> Thanks Biff
> I thought my changes had worked but found that the first record in the
[quoted text clipped - 34 lines]
>>> TIA
>>> Chris

Reply to this Message