Sum Products that meet a criteria
Posted by Eric on October 30, 2001 10:02 AM
With the following data:
A B C D E
1 Labor Material Percentage Labor Material
2 1000 2100 10% 100 210
3 900 1900 0% 0 0
4 800 2000 80% 640 1600
5 1000 2200 35% 350 770
6 1090 2580
7 Labor 1,090
8 Material 2,580
Is there a way to arrive at the values in C7 and C8 without adding the formulas in columns D and E? I've tried a variety of CSE formulas including combinations of SUM and PRODUCT, but no luck. Obviously my real spreadsheet contains much more data than this.
Posted by Barrie Davidson on October 30, 2001 10:15 AM
What is your criteria? Is it retrieve values from column A and B if column C (percentage) is blank?
BarrieBarrie Davidson
Posted by Eric on October 30, 2001 10:22 AM
It would've been clearer if the spaces weren't removed. I'd like to sum the products of A and C (labor x percentage) if the percentage is greater than zero. And then the same for material (B and C).
Posted by Aladin Akyurek on October 30, 2001 10:26 AM
Am I missing something?
Eric,
It seems you want
=SUMPRODUCT((A2:A5)*(C2:C5)) for Labor and
=SUMPRODUCT((B2:B5)*(C2:C5)) for Material.
However, I fail to see the point of the above exercise. If you have many rows of data, you'll incur a considerable degradation in performance.
Any comments?
Aladin
=========
Posted by Barrie Davidson on October 30, 2001 10:32 AM
Eric, how about using a formula in an adjacent column and then summing that column. For example, use the following formula for the labour portion.
=IF(C1>0,A1*C1,0)
Then copy this formula down and sum that column. Also do the same for materials.
Does this work for you?
BarrieBarrie Davidson
Posted by Eric on October 30, 2001 10:36 AM
Re: Am I missing something?
Aladin,
That's close, but there's one more thing to add - my criteria. I have some percentages that are equal to -100%. I don't want those multiplied by the labor or material values and I don't want them included in the sum.
Posted by Aladin Akyurek on October 30, 2001 10:40 AM
Re: Am I missing something?
Now I see some light:
=SUMPRODUCT((A2:A5)*(C2:C5)*(C2:C5<100%)) for Labor and
=SUMPRODUCT((B2:B5)*(C2:C5)*(C2:C5<100%)) for Material.
Aladin
========
Posted by Eric on October 30, 2001 11:00 AM
Re: Am I missing something?
Aladin,
Thanks for the help. After some playing around, I got it to work perfectly. Thanks for putting me on the right track. Sometimes it's hard to get back once you're off in one direction!
