How get user input that can then be used in formula, as criteria?

[jcoelho]

I have a table of information. I need to get a sum of one column based on
various criteria. I am currently using the SUMIFS function. (=SUMIFS(S2:S50,
C2:C50, "=Neil, Carla", I2:I50, "=Math", L2:L50, 1) However, I need various
sums, with the criteria constantly changing, and the worksheet changing each
month. Can I set up a dialogue box which will prompt the user to input the
criteria, then have the formula use the indicated criteria to produce the
desired sum?

 

[ShaneDevenshire]

Hi,

Change the formula to read:

=SUMIFS(S2:S50, C2:C50, A1, I2:I50, A2, L2:L50, 1)

And tell the user to enter the criteria into A1 and A2. Don't need a dialog
box.

If you have conditions that do require operators like >, <, >=, <=, or <>
then modify the above to read:

=SUMIFS(S2:S50, C2:C50, ">="&A1, I2:I50, "<>"&A2, L2:L50, 1)

There is no need for the "=" operator.