+ Reply to Thread
Results 1 to 21 of 21

Compare values from 2 tables and return total sum depending on drop down menu selection

  1. #1
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Compare values from 2 tables and return total sum depending on drop down menu selection

    Hi,

    Is there a way (formula) to compare all values (items) in a table at once (columns and rows; $D$2:$M$6) against 2 columns in a second table (items & items sold; $B$11:$C$500), and return the total amount (items sold) depending on a selection made with a dropdown list..?

    However, I'd like to be able to change or add items in the 'green' table columns in the future whitout breaking the formula.

    I think this is a real challenge, please see example for more info, because it's kind of hard to explain... ;-)

    Thanks in advance..!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    See attached:

    in G11

    =SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D2:I2))+SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D3:I3))+SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D4:I4))

    in G13

    =SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D3:I3))
    Attached Files Attached Files

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    f11=SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom1]=E11,Tabel4[[Kolom2]:[Kolom12]],0)))
    Try this array formula (confirm with Shift+Ctrl+Enter)
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Quote Originally Posted by JohnTopley View Post
    See attached:

    in G11

    =SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D2:I2))+SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D3:I3))+SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D4:I4))

    in G13

    =SUMPRODUCT((Tabel2[Items Sold])*(Tabel2[Item]=D3:I3))
    Hi John,

    First off; thanks for your reply.

    I've been working with your solution, but I can't get the correct Total Items Sold in G11 when I change the Drop Down Menu in E11 to Drinks. Same thing with the second formula in G13, which is depending on the values Green Fruit, Red Fruit, Yellow/Orange Fruit, Soda's & Juices hidden in a Drop Down Menu in E13...

    Any tips on how to enhance your formula to tackle this problem..?

    Thanks in advance...

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    The formula provided by NFLSALES is generic as you required so use this (mine were not!).

    in G11

    =SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom1]=E11,Tabel4[[Kolom3]:[Kolom12]],0)))

    I changed Kolom2 to KOLOM3 as I believe KOLOM2 is a "sub-heading": change back if I am mistaken

    the formula for G13 is:

    =SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom2]=E13,Tabel4[[Kolom3]:[Kolom12]],0)))

    Enter both with Ctrl+Shift+Enter

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    The formula provided by NFLSALES is generic as you required so use this (mine were not!).

    in G11

    =SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom1]=E11,Tabel4[[Kolom3]:[Kolom12]],0)))

    I changed Kolom2 to KOLOM3 as I believe KOLOM2 is a "sub-heading": change back if I am mistaken

    the formula for G13 is:

    =SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom2]=E13,Tabel4[[Kolom3]:[Kolom12]],0)))

    Enter both with Ctrl+Shift+Enter

  7. #7
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Quote Originally Posted by nflsales View Post
    f11=SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom1]=E11,Tabel4[[Kolom2]:[Kolom12]],0)))
    Try this array formula (confirm with Shift+Ctrl+Enter)
    Please Login or Register  to view this content.
    Hi nflsales,

    It seems that your formula is working as advertised :-D.
    Do you see that great big smile at the end of the last sentence..? That's me being happy..!

    Thanks a lot..!

    However, I'd like to ask you one more question...:
    I had to split the Fruits & Soda's by using the Convert Text to Columns wizard first. Is there a way to use the (or a) formula on a cell with these values divided by comma's..?

    So instead of using a range from $D$2:$M$6 with the different items, just one column ($D$2:$D$6), where in this case the cell value of $D$2 would be:
    Lime,Kiwi,Pear,Green Apple,Avocado,Melon,Casava,Olive,Green Grapes

    Thanks in advance for sharing your wisdom with us...

  8. #8
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Quote Originally Posted by nflsales View Post
    f11=SUM(SUMIFS(Tabel2[Items Sold],Tabel2[Item],IF(Tabel4[Kolom1]=E11,Tabel4[[Kolom2]:[Kolom12]],0)))
    Try this array formula (confirm with Shift+Ctrl+Enter)
    Please Login or Register  to view this content.
    Hi nflsales,

    It seems that your formula is working as advertised :-D.
    Do you see that great big smile at the end of the last sentence..? That's me being happy..!

    Thanks a lot..!

    However, I'd like to ask you one more question...:
    I had to split the Fruits & Soda's by using the Convert Text to Columns wizard first. Is there a way to use the (or a) formula on a cell with these values divided by comma's..?

    So instead of using a range from $D$2:$M$6 with the different items, just one column ($D$2:$D$6), where in this case the cell value of $D$2 would be:
    Lime,Kiwi,Pear,Green Apple,Avocado,Melon,Casava,Olive,Green Grapes

    Thanks in advance for sharing your wisdom with us...

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    attach a sample file

  10. #10
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    nflsales,

    Here a sample file...

    Thanks in advance.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    @JohnTopley,

    That's indeed working for me and you're right about kolom2 & kolom3. You guys are great..!

  12. #12
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Quote Originally Posted by nflsales View Post
    attach a sample file
    Hi nflsales,

    I guess I'm out of luck with regards to a formula that can use the comma seperated values..? Too bad.
    Luckily, your formula where I use the text to column converted values does work for me, although using it extensively does take a long time to calculate, especially when the table of items and items sold is very long. Is there perhaps an alternative route to calculate the items sold..?

    In my example I've managed to get results for Cat 2 using this formula:

    =SUMPRODUCT(--(Tabel2[Day]=J$12);--(Tabel2[Time]=$I13);--(ISNUMBER(MATCH(Tabel2[Item];$G$11:$G$20;0)));Tabel2[Items Sold])
    But the $G$11:$G$20 in there is refering to an extract from my items table Tabel4[[Item 1]:[Item 10]], and I'd rather not use a in between referal...

    Is there a way to use such a formula for Cat 1..?

    Thanks in advance.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    PS. My data comes from an ODBC connection, so perhaps that's why the formula calculations take such a long time (around 50/60 secondds). The last formula I'm using for Cat 2 though, is quit fast (less than 5 seconds). If I could only extend that formula to get results for Cat 1...

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    See attached.

    I have added a "Category" column (Column1) to your data table and a columnar table for details of products which makes the calculations much easier.

    I don't know if this is acceptable to you.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Hi JohnTopley,

    Thanks for your help, I appreciate it..!

    The Category column I can live with, as a matter of fact, I've already been looking into to this, but I'm still looking for the right formula to search for a value (item) in a matrix array (item 1-10 or $D$2:$M$6). The list of categories in my example file is just a small part and much, much bigger in reality and therefore I'd like it to be as dynamic as possible. Unfortunately the columnar table is prone to errors on such a large scale, and subject to much maintenance at that...

    But I think it's definitely a step in the right direction, and gives me something to work with for sure.

    Thanks..!!!

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    For (my own!) completeness I updated this for CAT1 and added an ITEM table: I except the latter drop-down could be rather long but this seems unavoidable

    Re your feedback: I don't understand why column data format (vs. row format) is a problem: other than the way you receive your data which I believe has to be reformatted ?

    What are the volumes of data i.e CAT1 , CAT2 and ITEMS?
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    First of all my sincere thanks for the work you've put into this...

    Volumes for:
    CAT1 up to 10/20
    CAT2 up to 30/40
    ITEMS up to 300/400
    They are subject to change over time, and total data can be 100.000 rows depending on (start & end) date selection.

    With regards to my concerns about the different layout of Cat, sub Cat and Items:
    In the original layI've got Fruit - Green Fruit - 9 Items (11 different entries in total). In the new layout the same Items need 30 entries to be categorized properly.
    So if these items expand in number I'm afraid the total amount of entries wil grow exponentially.

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    I don't now how you worked out 30 entries: my table only contains 23 to categorise everything in your sample! Green Fruit is 9 entries INCLUDING CAT1 & CAT2 (3 columns). Am I being extremely dumb?

    I understand the large volume of input data but 300/400 items split into CAT1 and CAT2 does not seem too onerous to me.


    I am obviously missing something

  19. #19
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    I'm the last one to consider you as being dumb, it's probably the other way around, hahaha...

    I'm sorry, Green Fruit has indeed 9 items assigned. However, in the extra table you made (column S,T,U) Strawberry was misplaced as a Green Fruit and I counted that as well, because I didn't notice at first. Also: Grapefruit is categorized as a Red Fruit, Which should be Yellow/Orange Fruit. And that proves my point how prone to errors this can be, if even you make 'mistakes' like that.
    (btw. my definition of an entry is: each cell that I need to fill with a value, so in case of Green Fruits I need to enter 9 times Fruit, 9 times Green Fruit an the 9 Items (27 entries), where in my original setup it was 1 Fruit, 1 Green Fruit and 9 Items (11 entries)).

    The thing is, we work a lot with excel, so we'll figure it out somehow eventually. But the people that get to work with it are in most cases total noobs. They learn just 1 thing, can repeat that indefinitely, but can't figure out what's wrong when SHTF. So the less they can mess up, the better for me :-)

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Understand! The errors in the list I made were my copy and past errors so I accept these can happens (which ever way you enter data).

    Equally it would be fairly easy to use VBA to transpose the data from the row format (which I believe is text-to-column exercise) to column format: if we got the coding right, there would be no errors!!!!

    Anyway I'll leave it that and hope you find a satisfactory solution.

  21. #21
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Compare values from 2 tables and return total sum depending on drop down menu selectio

    Thanks John, you've been a great help and definitely broadened my horizon..!

+ 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. Cost total based off of selection on drop-down menu
    By dreamsteve in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2014, 05:12 PM
  2. Replies: 1
    Last Post: 03-27-2013, 07:04 AM
  3. drop down menu selection populates column values
    By rebe29cca in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2011, 06:33 PM
  4. Replies: 0
    Last Post: 06-15-2011, 10:36 AM
  5. Return specific numbers from a chart depending on drop down box selection
    By JasonTV in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-12-2011, 08:22 AM
  6. Changing values based on drop down menu selection
    By henro8 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2010, 03:47 AM
  7. Changing cell values depending on drop down selection
    By a022227 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-14-2008, 10:35 AM

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