• Need Excel help (2000 SP1)


    I am trying to create a custom pricing sheet that can be used for pricing a commerical software offering for the company I work for. Due to non-disclosure, I have adjusted the sheet to make it generic, yet hopefully get the problem across corrrectly.

    In the attached sheet, a customer will choose one of the packages offered. The saleperson will put a quantity (usually 1) in one of cells B5:B8. In order to calculate the value in C11:C13, I need to know what package was chosen by the salesperson. With that number, I can get an index into the array at the bottom of the attached sheet to look up other values. I can work with functions like Match, VLookUp, Index, etc. But I can’t seem to figure out a clear and easy way to get the row number of the B5:B8 cell that has the 1 in it. I played around with some nested IF’s but it was getting kind of messy. And I don’t know VBA and would prefer not to get invoved with that for this one problem. Any help much appreciated!

    Viewing 0 reply threads
    • #600199

      I don’t think you need to use lookups. If I understand your question, you can use the SUMPRODUCT function to calculate what you want.

      See the attached worksheet. If I misinterpreted your problem, please explain in more detail what you want to calculate.

      BTW, I changed the formula in D10 from =SUM(D5:D7) to =SUM(D5:D8). And I removed the links to an external worksheet, because they caused error messages.

      • #600451


        WOW… That is exactly what I needed to do. Thanks!!!!

        • #600458

          Uhhh – what’s happening here? You asked an additional question, I started to reply to it, and now the question has disappeared.

          Anyway, upon looking back, I noticed that I made an assumption that may or may not have been justified, and that I made a mistake in the original attachment.

          So I’ll post part of the reply I composed:

          Please note that I have assumed that if you enter 3 in cell B6 (quantity of package 2), you’ll take 3*5 additional seats in Level 1. If you want 5 additional seats whatever the quantity in B6 is (as long as it’s > 0), use
          instead of

          And I’m afraid I that made a mistake in the formula in cell C13. In the original attachment, the formula was
          This just sums the cost of all options in D29:D32, which doesn’t make sense.
          I think it should be
          to take into account whether the option is taken (B13), the quantity (B5:B8) and the unit cost (D29:D32).

          I have attached the modified spreadsheet.

          • #600662


            Sorry – what happened was that I deleted the post you were trying to reply to when I realized I didn’t look closely enough at what was happening. I like that SUMIF function. Again, thanks so much for your quick and accurate response. You’ve helped me a lot!

    Viewing 0 reply threads
    Reply To: Need Excel help (2000 SP1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: