Here is the problem:
I need to be able to drive a SUMIF that is incorporating an array from a combo box as in the attached spreadsheet "Sumif Array Manipulation.xlsx" (sorry, I don't have upload privileges so I copy and paste images)
On the Summary tab you see an example of a combo box located at B2. Its input is from E3:E7 and the selection is stored under the combo box in B2.
-- removed inline image ---
The formula for "Total Sales:" is located in B5 and is constructed with a SUM wrapper around a SUMIF to handle array selections.
Note that when you change the combo box, "Total Sales" reflects the amounts attributable to the appropriate filter for all but "Eastern Region" which is a combination of two reps - hence the array return.
The problem occurs on the selection of the filter that returns the array. I need either to figure out how to edit/create the array that is shown in F4 so that it works like it is supposed to in B5 or how to change the formula in B5 so that it works against the array as enterred in F4.
By the way, the correct answer for the formula in B5 if the combo box is set to "Eastern Region" is shown in B7 which is replacing the index lookup of the combo box return value with the static text that it is supposed to emulate [=SUM(SUMIF(rep,{"Smith, Joe","Jones, John"},sales))].
Here is what the data looks like:
-- removed inline image ---
I need to be able to drive a SUMIF that is incorporating an array from a combo box as in the attached spreadsheet "Sumif Array Manipulation.xlsx" (sorry, I don't have upload privileges so I copy and paste images)
On the Summary tab you see an example of a combo box located at B2. Its input is from E3:E7 and the selection is stored under the combo box in B2.
-- removed inline image ---
The formula for "Total Sales:" is located in B5 and is constructed with a SUM wrapper around a SUMIF to handle array selections.
Note that when you change the combo box, "Total Sales" reflects the amounts attributable to the appropriate filter for all but "Eastern Region" which is a combination of two reps - hence the array return.
The problem occurs on the selection of the filter that returns the array. I need either to figure out how to edit/create the array that is shown in F4 so that it works like it is supposed to in B5 or how to change the formula in B5 so that it works against the array as enterred in F4.
By the way, the correct answer for the formula in B5 if the combo box is set to "Eastern Region" is shown in B7 which is replacing the index lookup of the combo box return value with the static text that it is supposed to emulate [=SUM(SUMIF(rep,{"Smith, Joe","Jones, John"},sales))].
Here is what the data looks like:
-- removed inline image ---
