PDA

View Full Version : Solved: sum values based upon 2 or 3 conditions



joeyc
07-15-2008, 02:13 AM
Background: I am trying to auto populate a sheet called DB1. I have been doing this by hand for some time now. However, I know it probably can be done somehow with the sum product function. I need some help getting started. This is a mini version of a very large problem I have.



1.) I have tables listed on sheet DB1. I would like to autopopulate these tables [only

yellow cells] based upon data that will be on worksheets called SourceOne and SourceTwo.

2.) The worksheet SourceOne is used to populate the 'Product AUM' chart in range B71:I83.

This worksheet has the following traits. The number of products are unknown. Here there are 3. However, there could be 10 or 5 or 2 or 25. Also, the number of rows for the columns called Client Types, TaxStatus, and Institutional for each product is unknown. One constant is the client. Only one client [in this case Long Term Capital] will be listed in a workbook. How I populated row 72 called Corporate? (see below) This is used for all client types listed in A72:A81 for all tables. I used this method to populate row 73, 76, and 79.


c72
===
Go to the worksheet SourceOne. Add up all values in column G for the first product called 'Emerging Markets Systematic' where the Client Type is Corporate and the Tax Status is Taxable.
D72
===
Go to the worksheet SourceOne. Add up all the values in Column G for the first product called 'Emerging Markets Systematic' where the Client Type is Corporate and the Tax Status is Tax Exempt.
E72
===
Go to the worksheet SourceOne. Add up all the values in Column G for the first product called 'Emerging Markets Systematic' where the Client Type is Corporate and the where the Institutional column (e) reads Institutional
G72
===
Go to the worksheet SourceOne. Add up all values in column F for the first product called 'Emerging Markets Systematic' where the Client Type is Corporate and the Tax Status is Taxable.
H72
===
Go to the worksheet SourceOne. Add up all the values in Column F for the first product called 'Emerging Markets Systematic' where the Client Type is Corporate and the Tax Status is Tax Exempt.
I72
===
Go to the worksheet SourceOne. Add up all the values in Column F for the first product called 'Emerging Markets Systematic' where the Client Type is Corporate and the where the Institutional column (e) reads Institutional

=======================================================

3.) The Worksheet SourceTwo is used to populate the data range B87:C90. This worksheet will only have 1 row of data called either: Commingled Fund, Mutual Fund: Retail Class, Separate Account, or Mutual Fund: Institutional Class per product.



I hope this isn't too much to digest. It is complicated learning how this information is organized. However, the task at hand is merely summing up the values based upon multiple criteria.

Bob Phillips
07-15-2008, 02:45 AM
I've done product 1 on source 1 for you, I leave the rest to you

joeyc
07-15-2008, 07:58 AM
I will look at this now.

joeyc
07-15-2008, 08:45 AM
The product names are 'hard-code.'

SUMPRODUCT(--(SourceOne!$C$1:$C$22='DB1'!$A72),--(SourceOne!$B$1:$B$22="Emerging Markets Systematic"),
--(SourceOne!$D$1:$D$22="Taxable"),SourceOne!$G$1:$G$22

I see 'Emerging Markets Systematic Written.

I need some type of macro to go to fill in the product names in cells: A68, A92, A116, A140, and so .... [the names could go in A'Cell Number' for every 24 rows.

Here is some code that mdmackillop wrote for me one day. I believe it will be helpful. It just needs to be tweaked so that the names of these products are displayed in row A starting at A68 and for every 24 rows.
A68
A92
A116
.... (etc.) ....

Sub FillRanges()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"IV1"), Unique:=True
If Range("IV3").Value = "" Then

Range("IV2").Copy
Else
Range(Cells(2, "IV"), Cells(2, "IV").End(xlDown)).Copy
End If
Range("H17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(Cells(1, "IV"), Cells(1, "IV").End(xlDown)).ClearContents
End Sub

joeyc
07-17-2008, 11:42 PM
Thank you xld.

I was do lots with that code. It certainly got me headed in the right direction.

I will mark this as solved.