At 13:20 15/06/2008 +0000, Laurent Duperval wrote:
I need some help writing an array formula in calc. Here is what it looks like:
I have a table with two sets of columns. One set of columns
represents a payment method (Cash, Credit Card, Cheque); another set
of columns represents the article purchased.
What I want is to calculate, at the bottom of the table, the total
amount received for each payment method. The price for each item is
in another cell in the spreadsheet.
I'm pretty sure I can only get by using an array formula, but I'm
not sure how to make it work.
It's always helpful to give a fuller explanation in cases like this,
or people will be working on and suggesting things to which you then
reply "Oh, no: I didn't mean that ...". Are your payment methods
text values, for example, or are they numeric identifiers? And when
you say the price is "in another cell" (!), do you mean that you have
a third column relating to your first two columns, or that there is a
separate look-up table of articles and prices?
I don't think you need an array formula here, in fact. Let's imagine
that your payment methods are in column A and your articles in column
B. And that you have a table of articles and prices in columns M and
N. Here's a suggestion.
o Construct a column of individual prices as column C. To do this,
put =VLOOKUP(Bx;M1:N99;2;0) in Cx and fill down. You can hide this
column if you prefer not to see it.
o To create your sums, use the SUMIF function, like this:
=SUMIF(Ax:Ay;"Cash";Cx:Cy) and so on. (The SUMIF criterion seems to
default to "=" without its being specified.)
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
