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:
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)