+ Reply to Thread
Results 1 to 9 of 9

Add quantities with changing results (due to array formula

  1. #1
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    129

    Add quantities with changing results (due to array formula

    Dear Forum.

    Not meaning to be 'milking it' by two posts on the one day but I got myself in much deeper than I originally anticipated and am now under pressure to make my workbook work shortly.

    The attached workbook is a simplified example of what I’m looking to do.

    In Cells D24:D32 I have array formula so that only the categories for the ‘desired house’ show up . The ‘desired house’ to be analysed is determined by the user selecting from the dropdown in Cell B23.

    What I’m looking for is a solution in Cells E24:E32 that will display the correct quantity for the given category (based on looking at corresponding values in J2:V10). Since the categories displayed are going to change in D24:D32 as well as B23 depending on the ‘house’ that is being analysed I’m at a bit of a loss.

    Any help would be great!!
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Add quantities with changing results (due to array formula

    This should do:
    =SUMPRODUCT(($I$2:$I$10=$D24)*($J$1:$V$1=$B$23)*$J$2:$V$10)

  3. #3
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Add quantities with changing results (due to array formula

    Lovely Job Paul! Thank you!!

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Add quantities with changing results (due to array formula

    No problem. Glad to help. Please marked as Solved if that took care of your problem.

  5. #5
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Add quantities with changing results (due to array formula

    I would Paull, except I'm having the same problem as I was on my earlier thread https://www.excelforum.com/excel-new...e-returns.html
    For some reason when I go into the original message it wipes out the original text. I can cancel out but I can't mark as solved as I get a message saying I need to enter a minimum of 10 characters (since it seems to be seeing none - as it seems to wipe out whatever was in the original.
    Admin/Moderator advise?
    Thank you again!!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Add quantities with changing results (due to array formula

    Off topic comment: When I logged in and saw a post from "Cidona", I thought: they just HAVE to be Irish.... and you are!!
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Add quantities with changing results (due to array formula

    Ah, you know the sweet apple beverage Glenn. Get any Tanora up your way Glenn?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Add quantities with changing results (due to array formula

    Nope. never seen it.

  9. #9
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Add quantities with changing results (due to array formula

    I guess it's more of a Munster thing. Keep an eye out for it the next time you're down that way https://en.wikipedia.org/wiki/Tanora

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula for identifying results in an array
    By purplelily in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-30-2013, 08:02 AM
  2. [SOLVED] Sorting results from an array formula
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2012, 06:17 PM
  3. Replies: 6
    Last Post: 05-26-2012, 04:56 AM
  4. [SOLVED] Invert Results of an Array Formula
    By jeversf in forum Excel General
    Replies: 7
    Last Post: 03-20-2012, 08:59 PM
  5. Survey Results Array Formula
    By braydon16 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2011, 01:02 PM
  6. [SOLVED] How do I change formula results by changing the text color of the
    By bailfire13 in forum Excel General
    Replies: 4
    Last Post: 05-31-2006, 11:45 AM
  7. [SOLVED] Rounding up results to meet minimum order quantities.
    By Vin C. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2006, 02:45 PM

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