Currently Being ModeratedDec 5, 2012 3:04 PM (in response to PhilipDebbas)
This syntax does not work in Numbers.
You can add an aux column to the table to make this possible like:
the Aux column is set up as:
D2=IF(A2="Apple", B2*C2, 0)
select D2 and fill down as needed.
Another way still uses and aux column but is more flexible to expand to other fruit:
The header of each aux column (column D in this example) determines whether a 1 or 0 is listed in the corresponding row:
D1=IF(A2=D$1, 1, 0)
select D1 and fill down. The first cell in the column contains the name of the fruit to search for in the column. To get the sum product use:
Currently Being ModeratedDec 5, 2012 3:13 PM (in response to Wayne Contello)
i know the syntax does not work in numbers, and i know i can add columns with intermediate results.
unfortunately, my real table is a lot more complex and i can't add columns for intermediate calculations. my real problem is that i have a table with weekly columns (52 for a whole year) with number of days per week per project and person and i need to check what the total billable time is per week per project per person:
column A is person over multiple projects, i.e. multiple occurances
column B is dayly rate
columns C-... are the number of days per week (52 columns in total)
under the whole sheet i have a smal table representing the people and i want ot see the total billable amount per person under eacht column C-... so i know the billable amount per week per person.
so i need to have this solved in one go without intermediate calculations. like the formula above in xls.
nevertheless, thank you for your response.
Currently Being ModeratedDec 6, 2012 9:22 PM (in response to PhilipDebbas)
Does the daily rate for a person change or is it always the same for that person? If it is a constant for each person, you can SUMIF the number of days for that person for the week, do a single lookup to get their rate and multiply by that rate. It's just a thought. I can see cases where a rate might change for someone over the course of a year: overtime in a particular week or during a particular project, a raise during the year, etc.
Otherwise, a solution would be to add 52 more columns to do the rate*days for each person for each week so you can do a simple SUMIF. You already know this as a solution, I'm just saying it is "only" 52 additional columns, not a completely outrageous number, and the formula for them is cut & pasteable.