Forum Discussion
How to assign hidden values to specific text in my dropdown data validation that is later sum
kirkBolton I'm not sure why that isn't working for you because when I opened it the numbers appear correctly. Please note that what Twifoo did was rearrange your lookup table to be in alpha-numerically increasing order (e.g. S1 then S10, ..., S14, then S2 because '1' is before '2' so 'S1...' is before 'S2...'). That said, here are 2 alternative suggestions you could try:
If you have XLOOKUP() I suggest using it:
=SUMPRODUCT(XLOOKUP(D6:J6,$AB$4:$AB$22,$AC$4:$AC$22,0,0))You could also use INDEX-MATCH combination to force an exact match (I added the IFERROR just in case but since you have data validation you could remove that)
=SUMPRODUCT(INDEX($AC$4:$AC$22,IFERROR(MATCH(D7:J7,$AB$4:$AB$22,0),1)))
kirkBolton I'm not sure why that isn't working for you because when I opened it the numbers appear correctly. Please note that what Twifoo did was rearrange your lookup table to be in alpha-numerically increasing order (e.g. S1 then S10, ..., S14, then S2 because '1' is before '2' so 'S1...' is before 'S2...'). That said, here are 2 alternative suggestions you could try:
If you have XLOOKUP() I suggest using it:
=SUMPRODUCT(XLOOKUP(D6:J6,$AB$4:$AB$22,$AC$4:$AC$22,0,0))You could also use INDEX-MATCH combination to force an exact match (I added the IFERROR just in case but since you have data validation you could remove that)
=SUMPRODUCT(INDEX($AC$4:$AC$22,IFERROR(MATCH(D7:J7,$AB$4:$AB$22,0),1)))
Your solution using =SUMPRODUCT(XLOOKUP(D28:J28,$AB$4:$AB$22,$AC$4:$AC$22,0,0)) worked perfect.
Thank you and thanks to everyone. I have learnt so much from the community. I am so grateful for this resource and the skills of you guys. Thanks
