How to use any formula with multi-select or drop down lists?

**heatham** · 03-31-2010, 01:10 AM

I have several drop down lists with text with multi-select enabled that I need to put into a SUM formula. How do I do this?

Here is one of my drop down lists:
QUALITY ISSUES (name of list)
Unusable donor images
Image format / specifications
Metadata conflicts
Poor quality image capture

I have numbers in a separate column that is associated with each of these quality issues that I need to add together in a SUM formula. Also, my IF formulas don't work when more than one quality issue is selected in the pull down (multi-select mode). Here's my IF formula =IF(D10="Metadata conflicts","4c "," ").

I need to add together all the image numbers for each category of 'obstacle'.

Thank you for whatever help you can provide.

Alan

**arthurbr** · 03-31-2010, 02:36 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**heatham** · 04-02-2010, 11:57 AM

I've uploaded a sample spreadsheet a couple days ago. I'm new to this forum, how do I get a response?
Thanks,
Alan

**Palmetto** · 04-02-2010, 02:38 PM

It's not entirely clear just what you want to sum.

Please edit your workbook to identify which cells to sum and what is the criteria. Provide a manual illustration of the results so we are clear on the logic. It would be beneficial if you included the validation lists so that the drop downs work.

Your drop down lists are Data Validation lists and they are not multi-selectable - any selection in any list is unique and no other selections can me made at the same time.

For your IF formula, you should use a look up table to match selections against the values you want returned, then use one of the look up functions (VLOOKUP, INDEX/MATCH formula, etc.)

**heatham** · 04-04-2010, 12:05 PM

Sorry about that, I forgot to add the category list to the spreadsheet to make the multi-select work correclty. I've uploaded the corrected spreadsheet.

I need to identify the total number of images associated with each category that users select in the multi-select cells.

Thank you for your assistance with this problem.

Regards,
Alan

**royUK** · 04-04-2010, 12:54 PM

I'm still not sure

**heatham** · 04-05-2010, 11:46 AM

Not sure of?

If you're not sure of what I need to add, then let me say more. I have several spreadsheets with projects around the world with the same obstacle list. I need to add the number of images associated with each unique obstacle from the pull down lists and aggregate the totals. So, the fundamental question is that I don't know how to use a formula on any cell with multi-select or pull downs. The formulas that I know for excel all require one number per cell to work properly. If you can show how this is done then I can find a way to aggreate the sums together from the various spreadsheets.

Regards,
Alan

**heatham** · 04-06-2010, 01:41 AM

Here's a formula that will work in my original workbook if there is only one selection made in the cell:
=SUMPRODUCT(--('North America2'!D9:D44="Outbound Linking"),'North America2'!C9:C44)

However, if there is more than one 'obstacle' selected in the pull-down list, then the above formula won't work. Any suggestions?

Regards,
Alan

**teylyn** · 04-06-2010, 01:53 AM

It would be helpful to have an inkling where the data is that you want to sum up. But since you say the Sumproduct is working with one condition, you may just need to add the other conditions.

Taking a guess here at where the data is:

=SUMPRODUCT(--('North America2'!$D$9:$D$44=D7),--('North America2'!$E$9:$E$44=E7),--('North America2'!$F$9:$F$44=F7),--('North America2'!$G$9:$G$44=G7),--('North America2'!$H$9:$H$44=H7),'North America2'!$C$9:$C$44)

That's assuming that in the sheet North America2 you have data in the columns D to H that corresponds with the Obstacle types in column D to H in your sample file.

If that does not do the trick, post a workbook that contains a North America2 sheet with some sample data.

cheers

**heatham** · 04-06-2010, 10:16 AM

Teylyn,

Thank you for your post. It's great to get assistance from a fellow cellist!

I'm uploading a sample of my spreadsheet with the formulas and the multi-select or pull downs options. You will notice that the formula doesn't work when I select more than one in one particular cell.

Let me know if you can tackle this challenge, I've been searching for a solution for days now!

Best regards,
Alan
(a cellist in Utah)

**teylyn** · 04-06-2010, 05:07 PM

Alan, I don't think it will work this way.

This looks rather like a task for a relational database.

How to use any formula with multi-select or drop down lists?

LinkBack

Thread Tools

Rate This Thread

Display

How to use any formula with multi-select or drop down lists?

Re: How to use sumif with drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Re: How to use any formula with multi-select or drop down lists?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions