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.

 

[Jackie Coelho]

Thanks Shane. However, I wonder if that is the best fix. You see, A1 is
already full, since I am pulling information from a spreadsheet that is
given to me. Perhaps I could insert a new row(s) and then create a separate
spreadsheet with all the possible combinations of searches I might need to
do, then copy and paste each month. But that too seems cumbersome and clunky.


url:http://www.ureader.com/msg/104235750.aspx