• 100% Satisfaction Guarantee

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1402
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

### Resolved Question:

Can you help me with another formula for the Excel report I'm working on? You helped me with one yesterday
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

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.

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1402
Experience: 20 years experience providing remote computer support

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

### What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland

• ### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair
< Last | Next >

### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair

### JasonJames122

#### Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

### Jess M.

#### Satisfied Customers:

301
Computer Software Specialist for more than 10 years

### The-PC-Guy

#### Satisfied Customers:

274
20 years experience providing remote computer support

125

### Chris L.

#### Satisfied Customers:

124
Certified Expert with over 10 years experience.

### IT Miro

#### Satisfied Customers:

97
Bachelor's Degree in Information Technology, Microsoft Certified Professional

## Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
The-PC-Guy
327 Satisfied Customers
20 years experience providing remote computer support