Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Countif, but only if

    right i am a little lost here, can someone please have a look for me...


    on the Tab london on my attachment

    i am trying to autopopulate the fields in the colums labelled quarter 1 ,2, 3, 4

    i only want the count to occur if the sales sheet, make of car and quater sold in match, i need to try and calculate how many of each car have been sold in each quater.

    Please help

    i am so lost

    Andy
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    i only want the count to occur if the sales sheet, make of car and quater sold in match, i need to try and calculate how many of each car have been sold in each quater.
    Okay, I looked at your workbook, now I have a few questions.

    1. What do you mean by "if the sales sheet"?

    2. "Make of car" - does this refer to "edition" on the London and Sales worksheets?

    3. On the Sales worksheet, you have a column for "Quarter" then you hvae "Quarter 3", etc. It would be better if you had just the number. Or if you will be using this cross-year, then something like 3Q-03.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Another suggestion for useability.

    Working with your form, it works fine, but you might want to consider changing the tab order so that if flows logically. Right now the tab order is such that the after the top two items are completed, the tab goes to the three action buttons at the bottom. What happens if a person, tabs and hits Enter on the OK button, will the person have to manually update the other columns? Will the form allow you to go back and complete it, or will you have to start over?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Mar 2003
    Posts
    225
    thanks for your reply.

    1. What do you mean by "if the sales sheet"?
    i meant worksheet named "Sales"

    2. "Make of car" - does this refer to "edition" on the London and Sales worksheets?
    Yeah . i meant the edition

    3. On the Sales worksheet, you have a column for "Quarter" then you hvae "Quarter 3", etc. It would be better if you had just the number. Or if you will be using this cross-year, then something like 3Q-03
    i really am happy to use whatever makes this work, so if you can suggest a better way i would really appreciate it. the only reason i used that is cos someone else suggested it.

    as long as the fields that indicate how many of each car i have sold in a specific period are automatically, i really am easy.

    Once again thanks for your help

    Andy

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, I changed the quarters on the worksheet "Sales" to numbers (1, 2, 3, 4)

    In cell I5 on London worksheet put this:

    =SUMPRODUCT((Sales!$B$6:$B$30="London")*(Sales!$F$ 6:$F$500=$C5)*(Sales!$H$6:$H$500=1))


    In cell J5 on London worksheet put this:

    =SUMPRODUCT((Sales!$B$6:$B$500="London")*(Sales!$F $6:$F$500=$C5)*(Sales!$H$6:$H$500=2))

    In cell K5 on London worksheet put this:

    =SUMPRODUCT((Sales!$B$6:$B$500="London")*(Sales!$F $6:$F$500=$C5)*(Sales!$H$2:$H$30=3))

    In cell L5 on London worksheet put this:

    =SUMPRODUCT((Sales!$B$6:$B$500="London")*(Sales!$F $6:$F$500=$C5)*(Sales!$H$6:$H$500=4))

    Then copy these formulas done to the bottom of the list.

    [Edited to add the following]

    Also, I used 6 and 500 as starting and ending points for the columns. If you think it is going to be more than 500 sales, then choose any number < 65,535. If you want you could name it as a dynamic range, so that it would always change to fit the size of the latest sales.

    And on the "Sales" worksheet you may want to move your ActiveX control ("New Sale") to the top of the page (above row 5) so that it won't cover the additional sales information.

    Hope this helps.
    Last edited by shades; 11-14-03 at 14:04.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    I also noticed that some of your formulas are not set properly.

    On the London worksheet, cell M5 should have this formula:

    =SUM(H5:L5)

    Then copy this formula down.

    Also, on same worksheet, cell O5 should have the formula:

    =M5*D5

    (The SUM is redundant).

    So, also for P5 ( =M5*G5)

    And Q5 ( =P5-D5*M5 )
    Last edited by shades; 11-14-03 at 13:45.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Here is an attachment that incorporates some of these changes.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Mar 2003
    Posts
    225
    thanks a million for your help

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •