1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Functions Attn: Firefytr & Bomb

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

Thread Status:
Not open for further replies.
Advertisement
  1. RGHmktg

    RGHmktg Thread Starter

    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

    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

    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?
     
  4. RGHmktg

    RGHmktg Thread Starter

    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
     

    Attached Files:

  5. Zack Barresse

    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. :D

    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:

  6. RGHmktg

    RGHmktg Thread Starter

    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

    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

    exegete

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

    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

    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

    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)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/628539

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice