Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 30th 08, 09:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1
Default 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?

  #2   Report Post  
Old July 1st 08, 12:25 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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?

  #3   Report Post  
Old July 1st 08, 04:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 1
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I request user in input variable value within formula? R Nelson Excel Worksheet Functions 11 June 3rd 08 01:29 PM
Prompt user for input and utilize that input ninner Excel Worksheet Functions 2 March 28th 07 09:44 PM
User Data Input DRANDON Excel Worksheet Functions 0 June 30th 06 08:12 PM
Have user input converted to uppercase in same cell as input? Shannonn New Users to Excel 1 June 20th 06 03:19 AM
How can I calculate user input from a combo box into a formula? Quizboy Excel Worksheet Functions 0 November 16th 05 07:11 PM


All times are GMT +1. The time now is 06:37 AM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017