weighted average with multiple criteria

Deborah Sellars

New Member
Joined
Sep 20, 2006
Messages
1
Worksheet consists of columns A-f with about 5000 lines of info
col A = text of one of four types ie(CD,DAT,ARI,VAC)
Col B = dollar amounts
col c = percentages (2 digits: ranging from 0.00% to 5.75%)
Col d = dates ranging from 01-01-2007 to 5-30-2010
how to do a CSE sumproduct with multiple criteria
a simple =sumproduct(b2:b5000,c2:c5000)/sum(b2:b5000)
answer is a % labeled the weighted average
trouble is doing this with multiple criteria for each range for example:
Rich (BB code):
=sumproduct(A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")b2:b5000) times (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")c2:c5000) / (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")b2:b5000)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

juanale81

New Member
Joined
Sep 18, 2006
Messages
5
I don't think sumproduct is a CSE formula, you would use CSE SUM to achieve the same result as a sumproduct. In your case, I think the way to go is using the formula you wrote but with sum instead of sumproduct (entering CSE of course).

{=sum((A2:a5000="CD")*(d2:d5000>=value("01-01-2007"))*(d2:d500=value("01-01-2007"))*(d2:d500=value("01-01-2007"))*(d2:d500="whatever condition"))}

One last thing, be careful to match every parenthesis.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Deborah
Welcome to the board

Please repost your formula. Use the Code button that you see in the Post Editor, so that we can see the whole formula.

I can't make sense of the date conditions that we can read

(d2:d5000>=value("01-01-2007"))*(d2:d500=value("01-01-2007"))*(d2:d500=value("01-01-2007"))

This means

The date must be after and including 1 January 2007 AND The date must be exactly 1 January 2007 AND The date must be exactly 1 January 2007

Besides repeating 2 times the last condition, the second condition makes the first one irrelevant.

The 3 conditions together are equal to:

The date must be exactly 1 January 2007


Can you please explain what you want the formula to do?

Also I don't see column C in the formula, but that's probably in the last part that we can't see.

King regards
PGC
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Worksheet consists of columns A-f with about 5000 lines of information
column A = text of one of four types ie(CD,DAT,ARI,VAC)
Col B = dollar amounts
col c = percentages (2 digits: ranging from 0.00% to 5.75%)
Col d = dates ranging from 01-01-2007 to 5-30-2010

how to do a CSE sumproduct with multiple criteria
=sumproduct(A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500 < value("01-01-2008")b2:b5000) times (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")c2:c5000) / (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500 < value("01-01-2008")b2:b5000)
I have not been able to get a formula to work - answer should be a %

=SUMPRODUCT(--(A2:A5000="CD"),--(YEAR(D2:D5000)=2007),B2:B5000,C2:C5000)/SUMPRODUCT(--(A2:A5000="CD"),--(YEAR(D2:D5000)=2007),B2:B5000)
 

juanale81

New Member
Joined
Sep 18, 2006
Messages
5

ADVERTISEMENT

Hello Aladdin,

what do the two minus signs before each parenthesis do? thank you
 

ethanle

New Member
Joined
Feb 9, 2010
Messages
4

ADVERTISEMENT

Hi All,
The formula works great. However, is it possible to include one more criteria (for example a name or something) beside the dates in the formula? If so how? Thanks.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hi All,
The formula works great. However, is it possible to include one more criteria (for example a name or something) beside the dates in the formula? If so how? Thanks.

Something like...
Code:
=SUMPRODUCT(
   --(A2:A5000="CD"),
   --(YEAR(D2:D5000)=2007),
   --(E2:E5000="Lucas"),
   B2:B5000,
   C2:C5000)/
 SUMPRODUCT(
   --(A2:A5000="CD"),
   --(YEAR(D2:D5000)=2007),
   --(E2:E5000="Lucas"),
   B2:B5000)
maybe?
 

Forum statistics

Threads
1,141,681
Messages
5,707,796
Members
421,529
Latest member
Balintn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top