Drop down and multiple criteria sumifs

[Caly]

Hi. I have 4 different columns with different criteria and totals. I want to make a drop down that will then feed into a summary total table based upon selecting various criteria in a drop down. Is there a formula I can use to sum the totals for whatever criteria may be chosen from the drop down without having to write a long formula of sumifs?

A	B	C	D
Total	Total	Total	Total
250	500	650	700

The drop down would be Based on the aboveTo select either A, B, C, or D independently or do various combinations such as A & B, B & C & D, and so on. Is there a formula to do such a total sum based on any type of combination that may be requested?

I want to make this as user friendly as possible as this will be for management to use and they may want to see any type of combination from the table; so ideally I am trying to make this as an input for them to select and then the summary beside the drop down would display the answer.

 

[Yongle]

I do not think I understand what you are asking (and therefore do not know if I can help )

My interpretation of post1

There is ONE total at the bottom of each of the 4 columns
- is that the subtotal of all the values of visible cells in that column ?

You want various combined totals based on a dropdown
- does selecting A & B in the dropdown (using your example) return 750 in the summary ?

 

[Yongle]

Hopefully this better explains what I think you may want

Row 1 uses subtotal function to return the sub-totals for visible cells in each column
The dropdown is a multi-select active-x listbox
The message box is triggered when selections in listbox are changed by the user

Unfiltered data
User selects B & D

Data has now been filtered

User selects B & D

User selects B & C

 

[Caly]

Thank you so much! My apologies, yes, the numbers For the columns are the totals and by selecting from a drop down any combination of columns the total would display (A,B,C,D, A and B, A and B and C, A and B and C and D, B and C, etc.).
So you suggest using the active-x and that will allow a user to select any combination?

 

[Yongle]

The above was a simple example which you can adapt to suit yourself, if you think you can make it work for your circumstances
It simply adds any combination of the 4 subtotals together (any ONE,TWO,THREE or all FOUR)

I named the sheet source "Detail"

You want the activeX listbox on your summary sheet ( I think)
- remember to amend mult-select property to MultiSelectMulti

The code uses A, B , C and D because they are the column letters you provided
- so it will require adapting if the listbox does not contain the column letters for selection
- think before jumping in: coding is simpler if the listbox values are column letters or column headers

I like my subtotals is in row1
- because it NEVER moves (it can be hidden) and therefore makes this line simple

total = total + .Cells(1, Col)

Pressing any non-special key when ListBox is active brings up the message box
- obviously that is only for illustration
- you will want the total placing in a cell I think

Let me know if any of the above is not clear or if you are struggling to tailor it to match your circumstances
- if your code is looking complicated then stop and ask
- all you are asking the code to do is to sum a few subtotals
- keep it very simple

Private Sub ListBox1_GotFocus()
    ListBox1.Clear
    ListBox1.AddItem "A"
    ListBox1.AddItem "B"
    ListBox1.AddItem "C"
    ListBox1.AddItem "D"
End Sub
'pressing any non-special key when ListBox is active brings up the message box
Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim a As Integer, total As Double, msg As String, Col As String
    With Sheets("Detail")
        For a = 1 To ListBox1.ListCount
            If ListBox1.Selected(a - 1) Then
                Col = ListBox1.List(a - 1)
                total = total + .Cells(1, Col)
                msg = msg & ListBox1.List(a - 1) & vbCr
            End If
        Next
    End With
'instead of message box you probably want the total written to a cell in sheet summary
    MsgBox msg & vbCr & total
End Sub

Private Sub ListBox1_LostFocus()
    ListB