+ Reply to Thread
Results 1 to 12 of 12

SUMIF or SUMPRODUCT or...

  1. 10-13-2012, 02:18 PM #1
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    SUMIF or SUMPRODUCT or...

    Hi All,

    I need to caculate on a daily basis the Weight Per Pack of Food Items that are distributed amost staff members. Attached is a sample of my data.

    I've tried the following formulas:

    =SUM(SUMIF(Sheet1!A:A,{"Apples","Bannas","Grapes"},Sheet2!$C:$D)) - Column B - Sheet 1
    =SUMPRODUCT((Sheet2!A:A="Sheet1!A3")*(Sheet2!A:A="Sheet1!B4")) - Column B - Sheet 2

    But success seems to be a distant relative at this stage......

    I need to be able to update values in Colum B of Sheet 1 using the data from Sheet 2. One important thing to be noted is that I can not in any case, merge Column C and D of Sheet 2 in Sheet 2, but at the sametime have to consolidate the weights in Column B of Sheet 1.

    Kindly review and advise...

    Looking for your expertise...

    Best Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-13-2012, 03:18 PM #2
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: SUMIF or SUMPRODUCT or...

    Perhaps something like this:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
    Reply With QuoteRegister To Reply
  3. 10-13-2012, 03:31 PM #3
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    Thank you!

    But I guess, there is one more thing that I'll need...

    The cells highlighted in yellow on sheet 1 exclude few products...So how does that get calculated. I think this will not do that, as it is unless I'm terribly missing something.

    Please advise.

    Thanks much!
    Reply With QuoteRegister To Reply
  4. 10-13-2012, 03:44 PM #4
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    Sorry for the late addition below, just popped up in my mind while I am reviewing the data...

    One more scenario in case where in sheet 2 column D there is a value, there is also a possibility that Column C also has a value. In such instances I'm required to eliminate the values for those products in Column C.

    Thanks in advance!
    Reply With QuoteRegister To Reply
  5. 10-13-2012, 04:23 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: SUMIF or SUMPRODUCT or...

    i think i have stayed withing your "rules". i used a helper column on sheet2 to use either C or D (or D if C had something too)

    then on sheet1, i used a simple sumif() to return the totals. take a look and let me know if this is something you can work with?
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 10-14-2012, 07:11 AM #6
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    As always, thanks FDibbins,

    I'll take a look and get back today.

    You rock!

    Thanks much!
    Reply With QuoteRegister To Reply
  7. 10-15-2012, 09:25 AM #7
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    This is fine, but I’m confronted with another challenge. Here is what it is like:

    I need to be able to use SUMIF to calculate the cost of each item based on an Array i.e. if 2 conditions are met. Could you shed some light on this please:

    Here are the conditions:

    Colum B: Vendor Name
    Colum A: Product type
    Colum I: Cost

    I would like to get the sum of cost returned in as total cost by Vendor Name and by Vendor Name by Product…

    I’ve attached the sheet with cost in column F...

    Please advise...

    Best reagrds
    Attached Files Attached Files
    Experience is not what happens to you; it's what you do with what happens to you.
    Reply With QuoteRegister To Reply
  8. 10-15-2012, 09:45 AM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMIF or SUMPRODUCT or...

    Maybe.

    In G2 and copy down.

    =SUMIF($B$2:$B$1000,B2,$F$2:$F$1000)

    In H2 and copy down.

    =SUMIFS($F$2:$F$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  9. 10-15-2012, 11:27 AM #9
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    Thanks Fortis,

    That is great, and working absoultely in line. But I'm sorry to come back, I have faced another challange which needs me to get the sum from 3-4 different columns depending upon the creteria.

    Could you please advise...

    Thanks much!
    Reply With QuoteRegister To Reply
  10. 10-15-2012, 11:30 AM #10
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMIF or SUMPRODUCT or...

    You can add as many conditions as you want in SUMIFS formula..
    Reply With QuoteRegister To Reply
  11. 10-15-2012, 11:48 AM #11
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    Hmmm....

    But my categories also keep on changing depending upon the column. Moe than that I need to pick values for 1 category from 1 column and the values for the categories from the other

    Does that mean I need to use 2 sumifs together or how...Please advise. I terrbily struck!

    Sorry for being a pain
    Reply With QuoteRegister To Reply
  12. 10-19-2012, 02:14 AM #12
    lifeisaspreadsheet
    lifeisaspreadsheet is offline
    Forum Contributor lifeisaspreadsheet's Avatar
    Join Date
    10-04-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    208

    Re: SUMIF or SUMPRODUCT or...

    Thanks everyone!

    I worked a way around using inputs from you all. It has been scucessfully accomplished.

    Appreciate your help:-)

    Marking it solved now... Cheers!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1