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

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?

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

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.

--
Cheers,
Shane Devenshire


"jcoelho" wrote:

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?

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

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