# weighted average with multiple criteria

#### Deborah Sellars

##### New Member
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.

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

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)

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

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.

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?

Works great.. thank you so much..

Replies
7
Views
378
Replies
10
Views
4K
Replies
2
Views
496
Replies
3
Views
497
Replies
0
Views
633

1,219,519
Messages
6,148,755
Members
450,833
Latest member
Andyboi

### 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.

### Which adblocker are you using?

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

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