Sum value from dropdown list menu

[Vani11]

Hi There!

I need some help to sum up the value for drop-down list. Each item in the drop-down list should have a value tagged with it.

This is the scenario:

[TABLE="class: grid, width: 444"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Subject Name[/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Appointment[/TD]
[TD]ID[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 6, align: center"]GENERAL SCREENING OPEN[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 135"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-Select'[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]DM[/TD]
[TD="align: right"]0.20[/TD]
[/TR]
[TR]
[TD]HV[/TD]
[TD="align: right"]0.30[/TD]
[/TR]
[TR]
[TD]RP DM/HV[/TD]
[TD="align: right"]0.25[/TD]
[/TR]
</tbody>[/TABLE]


Above is the table which i have created. When user selects the column "Type", 3 options will be displayed, (DM, HV & RP DM/HV - type of screening subjects). Each time when a user selects the desired option, it should begin to sum up the value that is assigned to each drop-down item. If the sum value is below 1.00, then the text will be displayed as "General Screening Open". If the sum of value is above 1.00 then the text will be changed to "General Screening Closed".

A user is entitled to select any options from the drop-down menu. If the user selects the following:

[TABLE="class: grid, width: 444"]
<tbody>[TR]
[TD]Subject Name[/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Appointment[/TD]
[TD]ID[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD] Subject 1[/TD]
[TD]DM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] Subject 2[/TD]
[TD]HV[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] Subject 3[/TD]
[TD]RP DM/HV [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] Subject 4[/TD]
[TD] HV[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 6, align: center"]GENERAL SCREENING OPEN[/TD]
[/TR]
</tbody>[/TABLE]

In this case, The total value is 1.05, hence the text should change to "General Screening Closed". The values should starting calculating when each data is entered.

I have tried to use conditional formatting but i did that only by inserting another column to indicate the designated value for each drop-down option. This time, i have tried using lookup function. I understand lookup function can handle only one criteria. Is there a way to handle more than 1 criteria by using index or match function?

Vani

 

[AndrewHoffenberg]

Hi Vani,

If I understand your question correctly the following should work:

Assuming "Type" is in column B:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>=IF(SUM(IF(B2:B10="DM",0.2,IF(B2:10="HV",0.3,IF(B2:B10="RP DM/HV",0.25,0))))>1,"General Screening Closed","General Screening Open")
ctrl+shift+Enter

-Andrew

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td] Type[/td][td] [/td][/tr]
[tr][td] -Select'[/td][td] Value[/td][/tr]
[tr][td] DM[/td][td]

0.2​

[/td][/tr]
[tr][td] HV[/td][td]

0.3​

[/td][/tr]
[tr][td] RP DM/HV[/td][td]

0.25​

[/td][/tr]
[/table]

Select A3:B5 and name it TypeTable in the Name Manager or via the Name Box.

[Table="width:, class:grid"][tr][td] Subject Name[/td][td] Type[/td][td] Status[/td][td] Appointment[/td][td] ID[/td][td] Time[/td][/tr]
[tr][td] Subject 1[/td][td] DM[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] Subject 2[/td][td] HV[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] Subject 3[/td][td] RP DM/HV[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] Subject 4[/td][td] HV[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

GENERAL SCREENING CLOSED​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

In A11 enter:

="GENERAL SCREENING"&IF(SUMPRODUCT(SUMIFS(INDEX(TypeTable,0,2),INDEX(TypeTable,0,1),B2:B10))>1," CLOSED"," OPEN")

 

[Vani11]

Hi Aladin


Thanks for providing me with the formula. It would be better to explain further. I will need to set the following conditions for the spreadsheet:

[TABLE="class: grid, width: 444"]
<tbody>[TR]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Subject Name[/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Appointment[/TD]
[TD]ID[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]GENERAL SCREENING OPEN[/TD]
[/TR]
[TR]
[TD="colspan: 6"]Summary[/TD]
[/TR]
[TR]
[TD][0] DM[/TD]
[TD][0] HV[/TD]
[TD][0] RP DM/HV[/TD]
[TD][0] New Subj[/TD]
[TD][0] Existing Subj[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


1) The text output message "GS Open" or "GS Closed" is dependent on two columns (Type & Status). This time, the text output message is based on calculating the values from the drop-down list of 2 columns (Type & Status).

2) To display "GS CLOSED" - The limit for the value is >=1.00

3) To display "GS Opened" - The limit value is between 0.00 to 0.90.

4) Once the value has reached 0.90, the cell color changes to "Amber" ( I did this using conditional formatting but after using your formula, it did not work)

5) These are the values assigned:

I have used your formula to add in the "Status" column.

="GENERAL SCREENING"&IF(SUMPRODUCT(SUMIFS(INDEX(CountGS,0,3),INDEX(CountGS,0,2)INDEX(CountGS,0,1),J7:J15))>=1," CLOSED"," OPEN")

This did not work.

This table name is CountGS
[TABLE="class: grid, width: 225"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]DM[/TD]
[TD]New[/TD]
[TD]0.30[/TD]
[/TR]
[TR]
[TD]DM[/TD]
[TD]Exisiting[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]HV[/TD]
[TD]New[/TD]
[TD]0.20[/TD]
[/TR]
[TR]
[TD]HV[/TD]
[TD]Exisiting[/TD]
[TD]0.20[/TD]
[/TR]
</tbody>[/TABLE]

I have another tab with drop-down values

[TABLE="class: grid, width: 225"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Appointment[/TD]
[/TR]
[TR]
[TD]-Select-[/TD]
[TD]-Select-[/TD]
[TD]-Select-[/TD]
[/TR]
[TR]
[TD]DM[/TD]
[TD]New[/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD]HV[/TD]
[TD]Exisiting[/TD]
[TD]Reschedule[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cancelled[/TD]
[/TR]
</tbody>[/TABLE]


6) The next requirement is when the user selects the options, ("Rescheduled" or "Cancelled") from the Appointment Column, the value limit should also be part of calculating whether the General Screening is "Closed" or "Opened"

I am trying not to use VBA coding as it might become very complex. Working on excel formulas at the moment.

 

[AndrewHoffenberg]

Hi, Small adjustment to the equation below. I left out the "B" after the 3rd if statement before the 10. It is included below and underlined:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>=IF(SUM(IF(B2:B10="DM",0.2,IF(B2:B10="HV",0.3,IF(B2:B10="RP DM/HV",0.25,0))))>1,"General Screening Closed","General Screening Open")

Hi Vani,

If I understand your question correctly the following should work:

Assuming "Type" is in column B:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>=IF(SUM(IF(B2:B10="DM",0.2,IF(B2:10="HV",0.3,IF(B2:B10="RP DM/HV",0.25,0))))>1,"General Screening Closed","General Screening Open")
ctrl+shift+Enter

-Andrew

 

[Aladin Akyurek]

1. With CountGS, we get:

="GENERAL SCREENING"&IF(SUMPRODUCT(SUMIFS(INDEX(CountGS,0,3),INDEX(CountGS,0,2),K7:K15,INDEX(CountGS,0,1),J7:J15))>=1," CLOSED"," OPEN")

2. If you also want to include Appointment...

="GENERAL SCREENING"&IF(SUMPRODUCT(L7:L15,SUMIFS(INDEX(CountGS,0,3),INDEX(CountGS,0,2),K7:K15,INDEX(CountGS,0,1),J7:J15))>=1," CLOSED"," OPEN")

Are we getting the results you intend?

 

[Vani11]

Hi Aladin,

Would there be a way to link a dropdown list value to a user form? The appointment column "D" has drop-down list.

The drop-down list values are:
Confirmed
Rescheduled
Cancelled

If a user selects, "Rescheduled", a pop up form should appear to users to select the following checkboxes:

*Blood Test *Urinalysis * Blood Pressure

Next, when the user mouse over that row of data where the appointment is "rescheduled", it should show the checkbox which the user have selected.

[TABLE="class: grid, width: 486"]
<tbody>[TR]
[TD]Subject Name[/TD]
[TD]Type[/TD]
[TD]Res[/TD]
[TD]Appointment[/TD]
[TD]ID[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DM New[/TD]
[TD]0.30[/TD]
[TD]Confirmed[/TD]
[TD]15605[/TD]
[TD]8:45 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]