Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Sum if with multiple options

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Australia
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to caluclate a formula based on the results of multiple other cells.

    At present I use the following formula which calculates firstly if the "sale" is within a certain date range, secondly if the area of sale is within an area, thirdly if it matches a group of sales area, fourth if it is not another type of sale and the fifth relates to the date range as well.

    Question - this forumla works well and counts the number of sales. However how do I make it now calculate a formula based on this sale information. Formula 2 represents the formula I want to calculate, but at present I have a seperate page that looks at 5000 lines of data and does 5000 calculations. I can't seem to get the 2 formulas into 1. Is anyone of any help (I'm ossyr if this is bloody confusing

    Formula 1.

    {=SUM(IF(Data!$T$2:$T$5000>Main!$B$2-8,IF(Data!$B$2:$B$5000=$A3,IF(Data!$D$2:$D$5000="Payment Services Officer",IF(Data!$U$2:$U$5000="",IF(Data!$T$2:$T$5000
    Formula 2.
    =IF(AND(Data!D2="Payment Services Officer",Data!Q2>Main!$B$2-8,Data!V2="Won"),SUM(Data!H2*Data!O2*12/100),0)

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,604
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-20 00:50, Shonky wrote:
    I need to caluclate a formula based on the results of multiple other cells.

    At present I use the following formula which calculates firstly if the "sale" is within a certain date range, secondly if the area of sale is within an area, thirdly if it matches a group of sales area, fourth if it is not another type of sale and the fifth relates to the date range as well.

    Question - this forumla works well and counts the number of sales. However how do I make it now calculate a formula based on this sale information. Formula 2 represents the formula I want to calculate, but at present I have a seperate page that looks at 5000 lines of data and does 5000 calculations. I can't seem to get the 2 formulas into 1. Is anyone of any help (I'm ossyr if this is bloody confusing

    Formula 1.

    {=SUM(IF(Data!$T$2:$T$5000 > Main!$B$2-8,IF(Data!$B$2:$B$5000 = $A3,IF(Data!$D$2:$D$5000 = "Payment Services Officer",IF(Data!$U$2:$U$5000 = "",IF(Data!$T$2:$T$5000 < Main!$B$2,1,0),0),0),0),0),0)}

    Formula 2.
    =IF(AND(Data!D2="Payment Services Officer",Data!Q2>Main!$B$2-8,Data!V2="Won"),SUM(Data!H2*Data!O2*12/100),0)
    I'd like to have some clarification if possible.

    1a) Activate an empty cell in Data, type =, select 10 rows of the data in T and U (including labels that you might have), hit F9, copy what you see after =-sign, and post it in the follow up.

    1b) Do the same for the data in B and D.

    2) Post what you have in Main!$B$2 along with what the constant 8 means.

    3) Post what you have in Data!D2, Data!H2, and Data!V2 and what they mean.

    As I understand it, you want to sum sales values that meet certain conditions.

    Aladin

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Australia
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    1a. {"...Date Referral received","..Type of Amendment required";37180,"Key Entry";37180,"MOTO";37246,"MOTO";37176,0;37180,"MOTO";37281,"Key Entry";37298,0;37186,"MOTO";37196,0;37258,"Key Entry";37190,0}

    1.b {" BUID NUMBER (XXXX-XXXXX)";"8173-47831";"7706-13716";"7706-40562";"8092-88659";"7709-57420";"8092-88659";"8090-80743";"7990-35673";"8090-81383";"8153-93738"}
    {" Consultants Title";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer"}

    2. Main!$b$2 is a date, being todays date.
    -8 means please go back a week + 1 day.

    D2 = Payment Services Officer (Job Title)
    H2 = 5000 (Volume of dollars for sale)
    V2 = Won (If it is a sale, ie a job can be another status, repair etc)

    "As I understand it, you want to sum sales values that meet certain conditions. "

    Yes, that is correct. Basically I have a database of data that I am doing reporting from, I dump it into excel. The reporting can be broked down by region (BUID Number), and I can count how many sales come from each region. I can't calculate what value collectively these sales contribue for each region. The sales have to be in a give time period (the last week), from a particular type of sales person (Payment Services Officer), if it meets that criteria I need to some the volume of sales projected for that sale (h2) times a particular discount rate (o2). I can calculate this no worries, but I can't get it to allocate these sales in to the region.

    Thanks!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,604
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Looking closely at your 2nd formula, I realize that you must have data also in columns O, Q, and V in the Data worksheet.

    Would you either post, following the procedure mentioned in our previous exchange, data from the columns I've missed in my previous request or send me a copy of your workbook?

    Regards,

    Aladin

    akyurek@xs4all.nl

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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