# Solved: Excel Functions Attn: Firefytr &amp; Bomb

Discussion in 'Business Applications' started by RGHmktg, Sep 24, 2007.

Not open for further replies.

Joined:
Aug 6, 2007
Messages:
53
OBP has solved my macro problems, and I now have my client's customer database in an Excel spreadsheet of 11,000 rows. But, I'm having trouble making common math functions like "sumif" and "if" work right to analyze the data. OBP recommends either of you as strong in functions. Could you pls e-mail me so that I can send you a real spreadsheet to figure out why the functions aren't working? Thx. Bob

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
See my PM. My only caveat would be that if you're using Excel 07, (a) Zack has it (b) it has additional functions.

3. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there!

Can you upload a sample file here? Are you just looking for a general auditing of your formulas and data structure, or are you looking for a specific outcome?

Joined:
Aug 6, 2007
Messages:
53
Firefytr and Bomb 21: Thanx for your offers of help. I've lobbed off most of my spreadsheet (there are actually 11,000 rows) and taken out the customer names to get it down to size. It seems that functions I've used before aren't working, so I must be doing something wrong.

The purpose is to identify the number of transactions and the amount of revenue by customer by geographical region. I've included two smaller regions, "8" and "7," Col F.

AK5 is supposed to display the sum of the number of transactions in Cols H thru M if the entry in Col F is "8." I'll copy that formula down the entire column, then do the same in Col AL for region "7."

AL619 is an alternate way of doing the same thing, based on creating Col N. I don't need a cumulative total column if I can do the summing function. Once the functions are working, I'll add more columns to the right for other transactions and revenue. I'll appreciate any thoughts you have.

Firefytr, I grew up in Portland. You anywhere near there?

Bob

File size:
176.5 KB
Views:
5
5. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hey Bob,

I grew up in Stevenson and lived in Vancouver for a while. So I know P-town pretty well. A lot of friends, and some family still there.

What you need I can surmise in two words: Pivot Table.

Keep column N. Base your pivot table off columns F and N. Your SUMIF() function is wrong, but with the pivot table you don't need it. Trust me, it'll be MUCH, MUCH faster than any amount of formulas you want.

In order order to make this effective you need to do some things. First of all keep your headers on only one row instead of breaking up the words into two rows. This is a common mistake seen and will play hell with keeping your spreadsheet lean and mean.

I've done this for you in your sample file and uploaded it back up. Let us know what you think.

#### Attached Files:

• ###### Comb Master Final ZRF.zip
File size:
241.3 KB
Views:
9

Joined:
Aug 6, 2007
Messages:
53
Firefytr...thanks for the prompt reply. Unfortunately, you blew me away. I don't know anything about Pivot Tables and don't begin to understand what you sent me. The macros issue took longer than I anticipated, and I'm now up against a deadline at the end of this week, so I don't have time to learn something new. The formula in AK5 worked in an old spreadsheet a year ago, but for some reason isn't working in this one, so I need to find something simple. OBP came up with a formula that seems to do the job: =IF(F5=8,(SUM(H5:M5)),0). I can easily copy it down the columns, total each, yet retain individual account detail. Any idea why my formula won't work when it has before? I really appreciate your help. Bob

7. ### jimr381

Joined:
Jul 20, 2007
Messages:
4,189
First Name:
Jim
I am not at a PC which will allow me to grab zip files from the site, but have you looked into a Pivottable for summarizations and grouping of data?

8. ### exegete

Joined:
Oct 26, 2005
Messages:
354
That's what Zack provided in his attachment.

9. ### jimr381

Joined:
Jul 20, 2007
Messages:
4,189
First Name:
Jim
Ah, ok hehe. I just offered it as a suggestion hehe. I am filling in at a PC and as such the user does not have rights to lots of file types. I apologize for the redundancy then.

10. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
RGHmktg, did you look at the file I uploaded?? Is that not going to work for you?? If not, why? Or what would you want changed? Pivot tables are the way to go here, definitely not the tens of thousands of formulas you are wanting to put in.

11. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
As I said via email, your formats in column F are, err, naff. Copy H8 (1), then select the region numbers and Edit > Paste Special > Multiply > OK.

Then you could use =IF(\$F3=AK\$2,SUM(\$H3:\$M3),"") in AK3, copy it across & down, and ditch the "Cume" columns altogether. (not that I'm knocking pivot tables, just answering that specific point)

As Seen On