Microsoft Office Questions? Ask a IT Expert for Support ASAP
Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question
would it be easier for you to send me the sheet
then you can describe what you need
Yes - you can upload it at http://www.ge.tt/#!/3ZRVHzo/v/0
ok i have it open
Here is what is needed:
Row 2 in columns C-L needs to reflect the sum of all Current Balances (column A) that pertain to that category (for instance column C category is Retail) Since various Collateral Desc in B would fall under one category, I have sorted the data and color coded it for ease in creating the formula, however column B is not usually sorted in order and one description would be found in cells disbursed in column B
The category apartments includes the following descriptions.
MULTIFAMILY 5 UNITS
Commercial RE - Multi Family
5+ UNITS RESIDENTIAL
The sum of all those descriptions would reflect $31,683,342,55 in the apartment category.
ok i get the vauge idea
i think however you would need to catagorize each row
since it has 3 different types
if you could add a column
that would make life much easier
Ok, that could be done, I think...
if you would like to do that
then send me an updated sheet
i could create the necessary formulas
u see excel doesn't recognize colors
they are just there to make it pretty for the user
Yes, I realize that, I only grouped them that way for ease for me. So the updated sheet should include more category lines, is that correct? For instance, there would be one for Multifamily 5+ units, etc.
so the values on top
across row 1
that match the colors in column B
add a column that has those values
matched to each row
then the formula to do this would be really simple
I'm pretty sure I understand what you mean. Do you want to sign off and I'll come back in a while?
i will be around
just post back here when you are ready to continue
i will check back every so often
Here is the link. I kept the values color coded, so you would know which would go under Retail, etc, for the formula.
lemmed download it
one slight problem
doesnt match the row 1 values
did you send me the correct sheet?
Just a sec
That's right - not all the values in column b pertain to the categories listed in columns C-L. The report has all loans in our entire loan portfolio. We only want totals for the ones I originally had color coded in column B - call them sub categories. Those are commercial real estate loans and those are the ones we are concerned with. And the total of those would fall into the main category. In other words, the sume of the current balances for all Multifamily 5+ units, Commercial RE - Multi Family and 5+ units residential would be in Apartments.
let me work from the original sheet then and try something
giveme a few minutes
then you will let me know if its what u need
Ok thanks. I know this could be done by simply sorting, but we want this to be automated.
ok see if this works for you
i added a column C
that has the same headings as go across row 1
i did the first few so you can see it as an example
as long as you match the headings in row 1 to the appropriate values in column C
it will automatically update the totals in row 2
despite the order
i hope this makes sense
If you need anything else you know where to find me
RATING NOW ENABLED
Yes, the first few look ok, but we would not be able to add the additional information you've added.
If that is part of the program it would be OK, of course.
We just need the totals for each category.
what i am saying
is if you copy the formula over to another worksheet
just add in a column C
with the header values as I did in this example
then the formula would work well
There isn't a formula in Column C, so wouldn't we need to go in and sort and add that information?
that is correct no forumla in C
the formulas in row 2
use the values in column C
3 separate values in column B
were consolidated into a single value
in column C
We would then have to add column C. We just want to work with the data that's already and have the formulas do the work.
i understand that
but it could get very complicated
Maybe it's more simple using thte second worksheet. I added all possible values that would apply into separate columns. Couldn't you just assign those values to one category and tally them. I know this isn't as difficult as it seems. I just may not be translating well.
I'm willing to pay more if we can work through this. I know you're spending a lot of time on it.
2nd sheet is confusing me even more
Let me get back to you, OK?. I think I can make it more clear.
Can you tell me how to get this multiple if function to return zero, rather than false?
=IF(B197="Mixed use Res/Comml",A197,IF(B197="Mixed Use",A197,IF(B197="Mixed - Retail / Office 1st TD",A197)))
are you still trying to do this sum thing?
let me try this a different way
ok, don't try to creat forumlas
if functions wont sum
let me try something else
If each cell either contains a dollar value or zero, can't the column be summed?
give me a minute
i got it working
did the first one as an example
see if that works for you
let me make one more change
do this one instead
this will allow you to copy the formula to other cells
then just change the values as you need
The loans will be sorted differently, so the sum needs to go at the top or at the bottom (row 2000) Also, the tally is for Apartments, not Retail. Otherwise, I think it is ok. I'll try to use the formula for the other columns. If it works, we're done on this one.
it will auto adjust
whether its 20 rows
or 2 million rows
yes just copy the formula to the other columns
and change the values
to the appropriate ones
Sorry - I got distracted. It works. Thanks! I have another that is really simple. Should only take you a moment.