+ Reply to Thread
Results 1 to 11 of 11

IF for arrays

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Question IF for arrays

    I'm working with 2 workbooks. WB1 is an order form that lists products and quantities (via manual data entry). I want WB2 to find all of the entries for one specific product (with multiple entries) on WB1 and populate the sum of the corresponding quantities on WB2.

    For example:
    A B
    Product Quantity
    36ry38 100
    32ry39 120
    36ry38 50

    I want WB2 to find all of the products "36ry38", sum the corresponding quantities (100+50), then produce the resulting value on WB2 (150).

    What is the best way to go about performing this function?

  2. #2
    Forum Moderator 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: IF for arrays

    =SUMPRODUCT((A1:A100="36ry38")*(B1:B100))
    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.

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,866

    Re: IF for arrays

    Please Login or Register  to view this content.
    Try this
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: IF for arrays

    Thanks, both of you! I am now able to throw in yet another condition and it's working!

  5. #5
    Forum Moderator 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: IF for arrays

    .......................

  6. #6
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: IF for arrays

    Now here's a curveball that I can't seem to figure out. When this formula produces "0", how can I change the "0" to a blank cell?

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,866

    Re: IF for arrays

    Use conditional format to make fontcolor equal to backgroundcolor if the result is 0

  8. #8
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: IF for arrays

    Never done that before. What is the function for that?

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,187
    Format the cell to display blank if the value is 0
    If posting code please use code tags, see here.

  10. #10
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,866

    Re: IF for arrays

    http://www.youtube.com/watch?v=3_p-IhrQtjQ
    Look at this youtube film
    Or google at conditional format for different examples

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,187

    Re: IF for arrays

    Here's a custom format that will hide 0.

    0.00;-0.00;

    You can changet the 0.00 to another number format.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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