Posts by firroo

    $1.5 paid to ozgrid paypal account
    Transaction ID:10Y597439K3742005


    Dear Experts,


    Can anyone help me to find a formula for the request I have. In the attached file you will see 2 sheets (BOE & LOC). What I need that in the LOC sheet that in column D that the formula looks at the part number (column A), Qty (Column B), & location (column C) and then give me the BOE's from BOE sheet that matches the 3 criteria's that I mentioned. For example in LOC sheet you will find part # AF2524600 with 6 qty under location P-C19-02-1US, the formula in column D have to check the BOE's numbers that matches the above criteria's from BOE sheet, the result will be in that cell that it will show 3 numbers [TABLE="width: 80"]

    [tr]


    [td]

    142414982992

    [/td]


    [/tr]


    [tr]


    [td]

    142415000143

    [/td]


    [/tr]


    [tr]


    [td]

    142415000143
    as the numbers sum above of the qty's are 6 and have the same location & part # if we compare it what we have in LOC sheet.


    Thanks in advanced.


    [/td]


    [/tr]


    [/TABLE]
    [TABLE="width: 80"]

    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: conditional formatting based on text & blanks


    Thank you for your reply.


    It didn't work with me the counta, but when I changed it to countblank it did the job. Thanks for your help.

    Quote from NBVC;762192

    Select frome A2:AI2 and go to Home|Conditional Formatting|New Rule


    Choose "use a formula to determine which cells to format" then enter formula:

    =AND($AF2="cancelled",Counta($AE2:$AH2)=0)


    Cllick Format and choose from Fill tab.

    Hi Experts,


    I need your help regarding order backlog formula.


    In sheet 1 I have the order data with many details, the important columns are column (D) which will have the delivery month & column (AV) which have the branch that the sale for.


    In sheet 2 I do have a drop down list where I choose the month in column (B2) which is Sep'15 and then a table like the below. On order backlog I want a formula that will calculate the sum of orders from oct'15 to Dec'15 by branch, If I choose the month Oct'15 in column (B2) then formula will be sum from Nov'15 to Dec'15 for each branch and so on, even if the chosen month went to year 2016 lets say Feb I will still get the sum from March,16 to Dec'16


    [TABLE="width: 397"]

    [tr]


    [td]

    Branch

    [/td]


    [td]

    Sales YTD

    [/td]


    [td]

    Order Backlog

    [/td]


    [td]

    GM% Sales YTD

    [/td]


    [/tr]


    [tr]


    [td]

    XYZ

    [/td]


    [TD="align: right"]11,310[/TD]

    [td][/td]


    [TD="align: right"]19.97%[/TD]

    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [TD="align: right"]7,166[/TD]

    [td][/td]


    [TD="align: right"]16.50%[/TD]

    [/tr]


    [tr]


    [td]

    DFG

    [/td]


    [TD="align: right"]7,508[/TD]

    [td][/td]


    [TD="align: right"]16.79%[/TD]

    [/tr]


    [tr]


    [td]

    RTS

    [/td]


    [TD="align: right"]3,298[/TD]

    [td][/td]


    [TD="align: right"]14.36%[/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [/tr]


    [/TABLE]
    [TABLE="width: 397"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Thanks In advanced.

    [/td]


    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [/tr]


    [/TABLE]

    Re: Sum with condition


    Hi,


    Thank you so much. This is so great, I appreciate your help.


    Wish you a good day.


    Re: Sum with condition


    Hi,


    Thanks for the help.


    For June the numbers% is incorrect, but for July its correct??


    Best regards


    Quote from Batman;754017

    Does this work? You can hide column C to avoid seeing the helper data.

    Re: Sum with condition


    Thank you for your interesting to help.


    good questions.


    1. I get the data for enquiries & wons from other sheet by using vlookup.


    2. Yes year will be added to the date, I was in hurry so that's why I mentioned the months without years, but in orginal format years will be there.


    I wish if an solution will be given. Thank you


    Quote from Batman;754009

    1. Where do you store, or how do you identify, the current month to form the basis of the calculations?


    2. You don't have a year identifier anywhere in the data. What is going to happen when you get to January? Do you compare across years, or do you not produce results for the first 5 months of the year?

    Re: Sum with condition


    Hi,


    Thanks for showing your interest in helping me.


    For your 2 questions:


    1. Yes if I use the formula you mentioned I get what I need.
    2. The answer is no my data is not sorted properly that's why I was looking for a formula to help me , maybe vlookup with match & index or other formula that I don't know.



    Best regards


    forum.ozgrid.com/index.php?attachment/66570/


    Hello all,


    Can any one please advise on a formula. in the attach file I want a formula on each rate cell for month june calculate sum of June,may,Apr wons's didvided by sum of mar.feb,jan enquiries.


    When I will move to July it will be sum of Jul,Jun,May won's didvided by sum of Apr,Mar,Feb enquiries. If I will drag the formula late to august it should calculate in the same way.

    Re: X axis in pivot chart shows decimal than percentage


    Hi Luke, Thank you for your interest in helping me. I am trying to attach & upload the file several times, but it wouldn't and I don't know the reason for that, I will check again later. Thank you so much.

    Quote from Luke M;726489

    Sorry for asking again, but could you upload to forum directly? I can't access outside sites. :(

    Hi All,


    I had created a pivot table and pivot chart for the data, the pivot table shows the row label's as percentages which is correct, but it when comes to the pivot chart it shows it as decimal, for example a 10% shows 0.1, I tried of formatting the x axis and choose percentages but it dosent want to change. Please help.



    Thanks

    Re: column chart with line chart & percentage


    Thank you so much for your help. This great. I have a question related to this, can the purchases be line trend and on the dots it shows the percentage, so I will have one column graph and one line only.

    Quote from pangolin;725101

    see if the attached file helps

    Re: Sumif with date criteria


    Thank you so much Pangolin, it works perfectly for the first part. Sorry that the second part was confusing, so I will put it into another sentence, so the weeks will not depend on the months (B2) I will be choosing any week for example week 10 & year 2014, so the formula will calculate the sales from weeks 1 to 9 for year 2014, I hope that's its clear and you can help me with an answer.


    Thanks

    Quote from pangolin;724370

    for the first formula YTD Sales Months - Current Month use this


    =SUMPRODUCT((sales!B3:B1520)*(YEAR(sales!A3:A1520)=YEAR(B2))*(MONTH(sales!A3:A1520)<MONTH(B2)))


    for the second it is not very clear since as per your list WS for 2014 the week numbers start from 25 till 36 and in the sales ws the date format are in DD/DD/YYYY format (ie the days are equal to month)...hence it is not very clear how the week numbers are to be identified...but in any case the formula will be somewhat similar to the one above