need help:
i have three columns. one has a list of fruits which can occur several times (three times apples, two times pears, etc.), the second has a list of prices and the third a list of quantities. how do i get the total value of my apple, i.e. the sum of the products of my apples?
in excel i write the following formula:
=SUMPRODUCT(--(A2:A101="Apples");B2:B101;C2:C101)
in numbers it just fails.
thanx
philip
MacBook Pro, OS X Mountain Lion (10.8.2)
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.
E6=SUM(D)
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:
E4=SUMPRODUCT(B,C, D)
dear wayne,
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.
philip
Can you post a respresentative example?
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.
thanx for asking. in fact the rate per person varies according to the project. this is why i need to use the sumproduct function, alas with a condition.
else it would have been easy indeed.
Looks to be still doable if each person's rate is constant within the project. Might require going to SUMIFS instead of SUMIF.
Regards,
Barry
conditional sumproduct
