PDA

View Full Version : VBA code to sum cells based on text in a cell in the same row but different column



bopha99
04-25-2014, 02:09 PM
I have an excel spreadsheet that I would like the numeric values in column H to be summed based upon if the cells in column F have the words equity or real estate investment trust. This amount would be totaled and divided by the total amount of the account. This would be the equity percentage of the account. The total amount of the account would be the sum in column H for the amount of rows that the same account number appears in column B. There would be a separate sum in column F if the cell has the words fixed income or cash and cash equivalents. There would be a sum of the values in column H if the word fixed income or cash and equivalents appeared in column F. This amount would be divided by the total amount to get the % fixed income of the total. This would be the done for each distinct account number in column B.

The output would then be on one row: account number, percent equity, and percent fixed income.

Does anyone have an idea how to run this in vba? Thanks.

SamT
04-25-2014, 05:05 PM
We will need to see an actual worksheet to do this. Please obfuscate any personal information on the sheet before you upload it.

You can upload attachments by clicking the "Go Advanced" button below the editor and Clicking "Manage Attachments" below the Advanced Editor.

p45cal
04-26-2014, 02:41 AM
Ideally, a sample spreadsheet please, but re:
sum in column F if the cell has the words fixed income or cash and cash equivalents..are we looking for cells which only have the text:
fixed income
or:
cash
or:
cash equivalents
or are we looking to see if any of those strings are contained within other text in each cell?

Jacob Hilderbrand
04-26-2014, 08:20 AM
Cross Posted Here: http://www.mrexcel.com/forum/excel-questions/773636-visual-basic-applications-code-sum-cells-based-text-cell-same-row-but-different-column.html

bopha99
04-26-2014, 10:35 AM
Thanks for the responses. I will have to upload a copy of the sheet on Monday. If VBA is too complicated, I suppose this could be done with some type of if(and( statement.

P45, I would be looking for cells that contain "fixed income" and "cash and cash equivalents"

bopha99
04-28-2014, 11:31 AM
I have attached a sample file with 2 accounts. Any help with this file would be much appreciated. Thanks.

p45cal
05-09-2014, 07:36 AM
In the attached workbook there are two buttons on the sheet. Click 'em.

The Formula version creates 2 areas of results, the upper area is a grid containing formulae where a column labelled Missing shows values for Asset classes which don't fit either of the other 2 categories (Income and Equity). I expect you'd normally see none of these, but the column serves as a check; if there are values under Missing it would mean that the search criteria need tweaking and/or the data in the Asset class column of your data may be incorrect.
The lower area is a copy, paste|values of just the columns you wanted to see in the results.

The pivot version does something similar, adding 2 categories (Income and Equity) but if any asset class doesn't fit one of these a third category Others is shown. You could copy, paste|values an area of the pivot table to give you just the summary you want to see.

bopha99
05-30-2014, 10:00 AM
Hey P45cal,

In the last sheet you attached, can you make the vba code compare the percent equity calculated to the percent equity in the target allocation column and then highlight the current percent equity cell red if the percent is 5% different than the target percent? For example, in the sheet you attached, for account 1455-0299, if the equity percent is less than 65% or greater than 75%, highlight that output cell red. Thanks in advance.