• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1936
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

Can you help me with another formula for the Excel report Im

Can you help me with another formula for the Excel report I'm working on? You helped me with one yesterday

The-PC-Guy :

The-PC-Guy :

ok

The-PC-Guy :

would it be easier for you to send me the sheet

The-PC-Guy :

then you can describe what you need

Customer:

Yes - you can upload it at http://www.ge.tt/#!/3ZRVHzo/v/0

The-PC-Guy :

ok i have it open

Customer:

Here is what is needed:

Customer:

Important information:

• There are 2000 rows, many of which are zero but will be used down the road.

• In this report, Columns A & B normally pull from another area, so the data is a formula, rather than a value.

Need:

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.

The-PC-Guy :

ok i get the vauge idea

The-PC-Guy :

i think however you would need to catagorize each row

The-PC-Guy :

since it has 3 different types

The-PC-Guy :

if you could add a column

The-PC-Guy :

that has

The-PC-Guy :

apartment

The-PC-Guy :

ect...

The-PC-Guy :

then

The-PC-Guy :

Retail

The-PC-Guy :

reatil

The-PC-Guy :

ect...

The-PC-Guy :

that would make life much easier

Customer:

Ok, that could be done, I think...

The-PC-Guy :

if you would like to do that

The-PC-Guy :

then send me an updated sheet

The-PC-Guy :

i could create the necessary formulas

The-PC-Guy :

u see excel doesn't recognize colors

The-PC-Guy :

they are just there to make it pretty for the user

Customer:

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.

The-PC-Guy :

i mean

The-PC-Guy :

so the values on top

The-PC-Guy :

across row 1

The-PC-Guy :

that match the colors in column B

The-PC-Guy :

add a column that has those values

The-PC-Guy :

matched to each row

The-PC-Guy :

then the formula to do this would be really simple

Customer:

I'm pretty sure I understand what you mean. Do you want to sign off and I'll come back in a while?

The-PC-Guy :

ok

The-PC-Guy :

i will be around

The-PC-Guy :

just post back here when you are ready to continue

Customer:

Ok

The-PC-Guy :

i will check back every so often

Customer:

Here is the link. I kept the values color coded, so you would know which would go under Retail, etc, for the formula.

The-PC-Guy :

ok

The-PC-Guy :

The-PC-Guy :

one slight problem

The-PC-Guy :

column B

The-PC-Guy :

doesnt match the row 1 values

The-PC-Guy :

did you send me the correct sheet?

Customer:

Just a sec

The-PC-Guy :

ok

Customer:

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.

The-PC-Guy :

let me work from the original sheet then and try something

The-PC-Guy :

giveme a few minutes

The-PC-Guy :

then you will let me know if its what u need

Customer:

Ok thanks. I know this could be done by simply sorting, but we want this to be automated.

The-PC-Guy :

ok see if this works for you

The-PC-Guy :

http://ge.tt/7MLVWzo/v/0?c

The-PC-Guy :

Customer:

Ok

The-PC-Guy :

that has the same headings as go across row 1

The-PC-Guy :

i did the first few so you can see it as an example

The-PC-Guy :

as long as you match the headings in row 1 to the appropriate values in column C

The-PC-Guy :

it will automatically update the totals in row 2

The-PC-Guy :

despite the order

The-PC-Guy :

i hope this makes sense

The-PC-Guy :

If you need anything else you know where to find me

The-PC-Guy :

RATING NOW ENABLED

Customer:

Yes, the first few look ok, but we would not be able to add the additional information you've added.

The-PC-Guy :

why?

Customer:

If that is part of the program it would be OK, of course.

Customer:

We just need the totals for each category.

The-PC-Guy :

what i am saying

The-PC-Guy :

is if you copy the formula over to another worksheet

The-PC-Guy :

just add in a column C

The-PC-Guy :

with the header values as I did in this example

Customer:

Just a sec

The-PC-Guy :

then the formula would work well

Customer:

There isn't a formula in Column C, so wouldn't we need to go in and sort and add that information?

The-PC-Guy :

that is correct no forumla in C

The-PC-Guy :

however

The-PC-Guy :

the formulas in row 2

The-PC-Guy :

use the values in column C

The-PC-Guy :

inother words

The-PC-Guy :

3 separate values in column B

The-PC-Guy :

were consolidated into a single value

The-PC-Guy :

in column C

Customer:

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.

The-PC-Guy :

i understand that

The-PC-Guy :

but it could get very complicated

Customer:

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.

Customer:

I'm willing to pay more if we can work through this. I know you're spending a lot of time on it.

The-PC-Guy :

2nd sheet is confusing me even more

Customer:

Let me get back to you, OK?. I think I can make it more clear.

The-PC-Guy :

okay

Customer:

Can you tell me how to get this multiple if function to return zero, rather than false?

Customer:

=IF(B197="Mixed use Res/Comml",A197,IF(B197="Mixed Use",A197,IF(B197="Mixed - Retail / Office 1st TD",A197)))

The-PC-Guy :

are you still trying to do this sum thing?

The-PC-Guy :

let me try this a different way

Customer:

yes.

The-PC-Guy :

ok, don't try to creat forumlas

The-PC-Guy :

if functions wont sum

The-PC-Guy :

let me try something else

Customer:

If each cell either contains a dollar value or zero, can't the column be summed?

The-PC-Guy :

give me a minute

The-PC-Guy :

i got it working

The-PC-Guy :

did the first one as an example

Customer:

ok

The-PC-Guy :

http://ge.tt/2Tiwjzo/v/0?c

The-PC-Guy :

see if that works for you

Customer:

ok

The-PC-Guy :

wait

The-PC-Guy :

let me make one more change

The-PC-Guy :

The-PC-Guy :

http://ge.tt/3LGOkzo/v/0?c

The-PC-Guy :

this will allow you to copy the formula to other cells

The-PC-Guy :

then just change the values as you need

Customer:

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.

The-PC-Guy :

The-PC-Guy :

whether its 20 rows

The-PC-Guy :

2000 rows

The-PC-Guy :

or 2 million rows

The-PC-Guy :

yes just copy the formula to the other columns

The-PC-Guy :

and change the values

The-PC-Guy :

to the appropriate ones

Customer:

Sorry - I got distracted. It works. Thanks! I have another that is really simple. Should only take you a moment.