+ Reply to Thread
Results 1 to 13 of 13

Sumproduct and Sumif

  1. #1
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Sumproduct and Sumif

    I've been stuck on this problem all day, I thought I figured it out with the help of Donkey but then I realized I did it all wrong..

    If you look in the attached sheet, I'm just trying to solve for two cells H6 and H7 in the summary tab.

    For H6:

    I'm trying to make it look up the supplier name in the cell above it H5 in this case its "Supplier 1" Then I need it to go to the "Input" tab and look up what part number Supplier 1 bid on. In this case its "HFT1" and "HFT2". and thats .30 and .20 . BUT... the Sub-Category, Category and Location need to match the Sub-Category, category and Delivery Location on the Input tab.

    Then if you look in the Decode tab for HFT1 and HFT2 they are under Delivered Price and are .76 and 1.49. Again... the Sub-category, category and location need to match.

    So then the formula needs to do this (.30-.76) * the EAQ for HFT1 in the Decode tab. In this case it makes it (.30-.76)*72300 and then it has to add that to (.20-1.49) * 3000 (this is for HFT2).

    I added a column in the Input tab in column Z which finds the savings from the old bid and the new bid. So maybe the formula can just Sum that column and multiply it by the EAQ and it only sums it for Supplier 1 when the Location, Sub Category, Category, and part number match.

    For cell H7 I'm just trying to get the price in the Decode tab which is under Q6. and multiply it by the EAQ. so .76 * 72300 and again the supplier needs to match what part numbers they bid on based on the Input sheet and match the category, sub-category and location.

    Any help is much appreciated.
    Last edited by teylyn; 01-14-2011 at 06:48 AM. Reason: restored original question

  2. #2
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Sumproduct and Sumif

    Can anyone tell me why

    Please Login or Register  to view this content.

    Doesn't work in cell H7? I just want it to give me a total for how much is being spent on Code 62, Fittings and Fort Payne by looking it up in Decode.
    Last edited by Tommy1005; 01-14-2011 at 02:55 AM.

  3. #3
    Registered User
    Join Date
    01-14-2011
    Location
    newdelhi, india
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Sumproduct and Sumif

    Hey!
    See the attached excel .. Check the formulae in Cells AA, AB, AC and AD of Input sheet and Column A in Decode Sheet.

    Since you need a match on category, subcategory and location, have done a concat on these fields on both the sheets and looked up the values in the Input sheet from the Decode sheet based on the concatenated string.

    Hope this helps
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sumproduct and Sumif

    Quote Originally Posted by Tommy1005 View Post
    I added a column in the Input tab in column Z which finds the savings from the old bid and the new bid. So maybe the formula can just Sum that column and multiply it by the EAQ and it only sums it for Supplier 1 when the Location, Sub Category, Category, and part number match.
    That is 100% the right thing to do - you can use the same formula you used for Col Z to generate the EAQ Savings in Col AA by simply changing the SUM range from Col Q (Del. Price) to Col N (EAQ)

    Please Login or Register  to view this content.
    Then modify H6 on Summary to use Col AA as sum rather than Col Z as is presently the case.

    Quote Originally Posted by Tommy1005
    For cell H7 I'm just trying to get the price in the Decode tab which is under Q6. and multiply it by the EAQ. so .76 * 72300 and again the supplier needs to match what part numbers they bid on based on the Input sheet and match the category, sub-category and location.
    You could create another SUMIFS on Decode to hold this value, however, given you have the Savings & EAQ values already you could equally use:

    Please Login or Register  to view this content.
    Then for H7 use the same as H6 but modify summation range from AA to AB.

  5. #5
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Sumproduct and Sumif

    I was able to figure out cell H7, and I got cell H6 too

    thanks
    Last edited by Tommy1005; 01-14-2011 at 06:05 AM.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sumproduct and Sumif

    You won't be able to use DSUM in any other row than row 6 however - the suggestions made are just adaptations of what you're already using and SUMIFS is pretty basic in truth.

    Also, not to sound like a broken record, re: post # 2 you should not be using SUMPRODUCT at all if performance is an issue (which it is - discussed previously)
    (it's not a route to go down irrespective of whether or not you can get SUMIFS etc to work)

  7. #7
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Sumproduct and Sumif

    Quote Originally Posted by DonkeyOte View Post
    You won't be able to use DSUM in any other row than row 6 however - the suggestions made are just adaptations of what you're already using and SUMIFS is pretty basic in truth.

    Also, not to sound like a broken record, re: post # 2 you should not be using SUMPRODUCT at all if performance is an issue (which it is - discussed previously)
    (it's not a route to go down irrespective of whether or not you can get SUMIFS etc to work)

    I was able to use the Dsum for everything because I made a criteria for each sub category, category and location, it was pretty easy i just referenced it in a different sheet and copy pasted down. Then I was able to just drag the dsum down.

    See attached for what I'm talking about, it's pretty handy function, I never knew about it until today.

    Also I know about the sumproduct on the Exclude sheet, its really bogging things down... I need to a new way to find the savings based on just the Supplier and what they bid on based on the Input tab, and then compared to the Decode tab.

    For example Supplier 1, bid on HFT1 and HFT2 which cost 22,590. Now go look in the Decode tab, and HFT1 and HFT2 cost 59,719. Now you just do 59,719 - 22,590 and thats the answer I'm trying to get in cell Q6 on the Exclude tab. I already have the answer there, but it's just bogging down my excel a lot...
    Last edited by teylyn; 01-14-2011 at 06:53 AM. Reason: restored original content

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sumproduct and Sumif

    Quote Originally Posted by Tommy1005 View Post
    I was able to use the Dsum for everything because I made a criteria for each sub category, category and location, it was pretty easy i just referenced it in a different sheet and copy pasted down. Then I was able to just drag the dsum down.

    See attached for what I'm talking about, it's pretty handy function, I never knew about it until today.
    I am aware of DSUM also, however, my comments were based on the set up of the model as we had last seen it - ie Sheet1 did not exist.

    I must say it all seems a little convoluted to me given SUMIFS does the same just as efficiently without the need for Sheet1, however, if you're comfortable with it that is the main thing.

    (that said I thought you had to differentiate based on Product code also - ie HFT1/2 etc... but perhaps I've misinterpreted the requirements again)

  9. #9
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Sumproduct and Sumif

    Thats right,

    Just needs to look at the supplier name, see what they bid on, so in supplier 1s case its HFT1 and HFT2 and you add column V so its 22590, and then it needs to look up in decode HFT1 and HFT2 and subtract 22590 from that #.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sumproduct and Sumif

    Tommy1005, I confess I struggle to follow your threads, I can never quite work out what's being talked about or where you/we are in terms of resolution.

    You've removed your prior post in which you alluded to the fact you were using DSUM (and backed this up with attachment).
    My point re: DSUM was that

    a) it seemed over engineered (and laborious)

    and

    b) I didn't think that would actually give you what you needed given you were not detailing the specific products (HFT*) and were you to that within the DSUM it would be a very manual process.

    As far as I can tell, ignoring Exclude sheet, I think you have the solutions you required within this thread so I'm afraid I'll be bowing out gracefully at this point.

  11. #11
    Forum Contributor
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Sumproduct and Sumif

    Thank you for your help, I think I'm on the right track, I removed the posts because it was getting confusing.. Thanks again!

  12. #12
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Sumproduct and Sumif

    Tommy1005,

    please do not change post that have been replied to. You changed the text of your original question several times, and finally it only read "Thanks for your help". You also deleted the text of one of your ensuing posts after a member replied to it.

    As much as we appreciate your thanks, please understand that the answers to your initial questions do not make much sense if you delete/change the original text of the question.

    Therefore, I've restored the first version of your initial question and the text of post #7. If you need to add any additional information to help the members here help you in finding a solution, please just post a reply to the thread, and leave the original wording of the question as it is.

    regards,
    Last edited by teylyn; 01-14-2011 at 06:54 AM.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sumproduct and Sumif

    @Tommy1005, last post... avoid repetition of formulae where results remain constant, eg:

    Please Login or Register  to view this content.
    as you can see - we need not repeat functions already calculated (SUMIFS, COUNTIFS, DSUM etc...) doing so just adds unnecessary overhead.

    In terms of G and DSUM - I'd still be inclined to use SUMIFS myself to avoid need for Sheet1, eg:

    Please Login or Register  to view this content.
    Though I would point out that the results of the above will differ to your DSUM values re: partial matches

    Akron, OH: Fittings: O-Ring

    DSUM returns 139,802.7 (ie inclusive of O-Ring Face Seal values)

    SUMIFS as above returns 1,537.63 (exact match with O-Ring)
    You can add wildcard functionality to SUMIFS if nec. but I don't know which of the above is correct obviously - presumably the former given you adopted it.
    (note the partial match will apply to other rows also - eg Delhi IA, Fittings, Quick Disconnet etc...)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1