SOLVED

# sum of two drop down lists

Occasional Contributor

# sum of two drop down lists

Hi,  I know this questions has probably been asked and answered, but I think I need a more direct answer.

16 Replies

# Re: sum of two drop down lists

``=XLOOKUP(pom,column_pom,column_p)+XLOOKUP(htm,column_htm,column_h)``

# Re: sum of two drop down lists

Let's say that your lookup tables are in A10:D20.

In A2:

=IFERROR(XLOOKUP(B2, B10:B20, A10:A20)+XLOOKUP(C2, C2:C10, D2:D10), "")

# Re: sum of two drop down lists

Hi Hans,

Thank you for responding.  I have further questions and will be repeating previous info for clarity.  (apologies for redundant information.   I am not good with this.)

There is a number populating the the desired cell, but the sum is not correct.  This is what I did:

in cell A2 (which reads 0)-  the formula I entered is:

=IFERROR(XLOOKUP(B13,BL[BL DESCRIPTION],BL[BL'#])+XLOOKUP(C13,BLHTM[HTM DESCRIPTION],BLHTM[HTM'#]),)

Cell B2 (where it says FRONT BODY LENGTH) is a drop down list linked to BL description column in ORANGE CHART

Cell B3 (where it says HPS to Skirt Overlap) is a drop down list linked to HTM description column in BLUE CHART.

You advised : =IFERROR(XLOOKUP(B2, B10:B20, A10:A20)+XLOOKUP(C2, C2:C10, D2:D10), "")

I followed the layout, but my data for the charts/drop down lists are located in another sheet.  And omitted "".  When "" are added, nothing populates.

These are my data charts:

ORANGE is BL chart

BLUE is HTM chart

My desired result is to have cell A2 read 101.04 when the formula is correct.

# Re: sum of two drop down lists

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: sum of two drop down lists

Yes, please see attached.  Tabs/sheets are highlighted in green and red.

The data charts are in red "data set(DO NOT USE PRINT)" tab

The cells that I want to populate using formula are in green "GRADED SPEC" tab.

If there is anything else I can make clearer for you, pls let me know.

Again, thank you for your time!

# Re: sum of two drop down lists

Does this do what you want?

# Re: sum of two drop down lists

Unfortunately, no.
I am not able to select other options in the drop down lists and have them sum correctly/without error.

# Re: sum of two drop down lists

Which version of Excel do you have?

# Re: sum of two drop down lists

I think I'm using Version 16.66.1? That's what it says in "about excel"

# Re: sum of two drop down lists

And the Office version? Microsoft 365, or Office 2021, or Office 2019, or ...?

# Re: sum of two drop down lists

I think its Microsoft 365.

# Re: sum of two drop down lists

In that case, I don't understand why it doesn't work for you, sorry. The result of the formula changes when I select different items from the drop downs in columns B and C.

# Re: sum of two drop down lists

OK, Thank you for your help.

# Re: sum of two drop down lists

Hi Hans,
I have found out that I have Excel for Mac 2019. Does this change anything?
best response confirmed by feistyb (Occasional Contributor)
Solution

# Re: sum of two drop down lists

Yes, that makes all the difference! XLOOKUP is only available in Microsoft 365 and Office 2021, not in Office 2019.