numbers
I'm switching from Microsoft Excel to Number.app, in Excel you can do
=SUMPRODUCT((A1:A5-A1)^2,B1:B5)
the a1:a10-b1 bit will return a cell range equivalent to a1:a10 but with all the cells decremented by b1. What's the proper syntax in Numbers for that?
If I try to import an Excel workbook using that syntax into Numbers, that formula will be imported verbatim and Numbers will complain.
Numbers 09 has the SUMIFS function, which operates the same as in Excel. Numbers 08 does not appear to have it, but it has SUMIF.
You should still be able to do what you are trying by combining two logical statements.
Without using the function, you could also build a new column by multiplying values by a logical statement. For example:
=A3 + A4 × (B4>2)
This will only add A4 if B4 is greater than 2...
Or in your example, make a column using AND:
=A2 x AND(B2<10,exact(C2,"female))
Then sum that column. Values that don't meet both criteria will be zero.
I had the same sort of problem and with some help from people within the Apple Support community I came up with this solution. Hopefully it will work for you.
This is my list of players in sheet 1. In your case, the Players name would be the item codes.
Here is sheet where I want to information copied to.
The P# corresponds to the number next to the Players name in Sheet 1. If I enter a 1 and a 2 the names are filled in to the column to the left of the box.
This shows the formula required to transfer the players name from sheet 1 to sheet 2.
To take it a step further, when I fill in the Players winnings in Sheet 1 it also fills it in all the "Earnings" cells for that player in Sheet 2 using the formula shown below.
My list of players goes to 300 but the last 20 or so are empty. That way I can still add more names or in your case, item codes, to it.
This may be more complicated than you require but hopefully it helps you.
Best Answer
In numbers, SUMPRODUCT (range, range…) takes the following arguments:
So you'll need to set up a range to perform the initial calculation (and squaring) and then sumproduct two simple ranges.