Forum Discussion
Help to match Fund Name to an array of possible entity Codes
Hi,
I have data which i am adding up based on criteria. I have a sumif which reads between the dates, however i need to match the fund Name. I have Column C listed in the "Monthly Upload" tab, and which to include it as a criteria for the sumif. The Fund Name, can be made up of varies classes, and as such can have many entity codes.
Can someone recommend a formula to match the column of Entity codes to an array of possible entity codes for the fund. I have attached an example.
Thank you kindly for any assistance.
4 Replies
To add to mathetes comments, I believe you make it more difficult than needed. In the attached workbook I added a few helper columns to the first tab. These determine that:
1) the date falls in the reporting month
2) the transaction type equals "Redemptions", as that is what you seem to be looking for.
3) it finds the row number for the entity code in the legend (from each of the four possible options)
4) based on 3) it finds the Fund Code
5) finally, it calculates the Units in case both 1) and 2) are TRUE
The formula in E in the Monthly Upload is a simple SUMIF.
In order to test the example, I changed one of the Transaction types to "Redemptions". It's very likely that someone else can create a very complex formula that does everything in one go. But I suspect that such a formula would not be very easy to understand or maintain.
It's very likely that someone else can create a very complex formula that does everything in one go. But I suspect that such a formula would not be very easy to understand or maintain.
Well said! Just because something can be done in the most difficult [and, not incidentally, impressive!] manner, doesn't mean it should be done that way.
Nesting functions and formulas within functions and formulas within.... is, admittedly, on occasion fun. But even if it works, it'll be next to impossible to decipher or debug when you get the inevitable #NUM or #REF or some other error message.
I'll go as far as nesting two or maybe three levels deep, but just keeping track of the parentheses gets tricky beyond that. And if it happens to be a shared spreadsheet, I'd just be creating work for myself in the future were something to go wrong.
All of that having been said, Excel is kind of amazing in that there is almost always a different route to the desired destination, often easier than the first tried.
Just walk around the mountain rather than trying to clamber over it.
I wasn't able to follow from your description what it is you want to have happen in your sample. However, it did sound possibly as if you could use the DSUM database function to retrieve the information you desire, using whatever criteria (including the date range; but certainly also the fund codes) you desire.
If that doesn't work for you, I would ask you to give a more specific example of what you want done here (or just try using other words; it may be my problem in following your description, and I apologize for that.)
Himathetes
Thank you for the message.
My apologies for the lack of clarity in the description above. I will try to describe clearer.
I have a list of funds which may have up to 4 different codes. This is shown in the legend tab. (see below).
My data is in the "u_109 report" tab, with the entity code being in Column A.
I wish to find the sum of column G of the "U_109" tab for example for each fund. As each fund has multiple entity codes, i wish for it to add up for all entity codes in Column A which relate to the fund, between the dates Given by:
'u_109 Report'!$B:$B,">="&Legend!$B$3, &
'u_109 Report'!$B:$B,"<="&Legend!$B$4)
I have attached an example, please let me know if this helps. I will try Dsum, but am unsure how to use in this example.
Thank you kindly for your time and assistance.
