Solved

How to create formulas on the fly in Excel 2010

Posted on 2014-11-29
6
176 Views
Last Modified: 2014-11-30
I want to have the values "32" and "41" within the formula "=COUNTIF(Numbers!$E$32:$E$41,A4)" calculated from the value of some cells, say A1 and A2.
0
Comment
Question by:ParkiII
  • 3
6 Comments
 
LVL 6

Accepted Solution

by:
Asif Bacchus earned 500 total points
ID: 40472236
So I'm assuming you want the row numbers to be in cells A1 and A2?  So if you have a list of 100 items, you want to enter A1=25 and A2=50 then count between rows 25-50 for the value you are seeking?  Is that correct?  If so:
=COUNTIF(INDIRECT("E"&A1):INDIRECT("E"&A2),A4)

Open in new window

would be what you are looking for.  INDIRECT takes a text value reference address and returns the value of that reference.  In this case we are constructing the string "A" & "value of cell A1".  Therefore, if A1=25, indirect returns A25 and that becomes the first address of the CountIF statement.

If you need the columns to be entered separately too, you can probably figure that out based on this example or you can post back and I'll give you that code also.

HTH.
0
 

Author Comment

by:ParkiII
ID: 40472243
I found the solution:

First
=COUNTIF(Numbers!$E$32:$E$41,A4)
was changed to:
=COUNTIF(INDIRECT("Numbers!$E"&(($A$1-1)*($A$2)+2)&":$E"&(($A$1)*($A$2)+1)),A4)
and
=SUMIF(Numbers!$E$32:$E$41,A4)
was changed to
=SUMIF(INDIRECT("Numbers!$E"&(($A$1-1)*($A$2)+2)&":$E"&(($A$1)*($A$2)+1)),A4)

Thanks, everyone that tried!
0
 

Author Comment

by:ParkiII
ID: 40472439
I've requested that this question be closed as follows:

Accepted answer: 0 points for ParkiII's comment #a40472243

for the following reason:

Thanks Andrew, see my comments.
0
 

Author Comment

by:ParkiII
ID: 40472245
Please, give Andrew Hancock the points, he earned them!  I just did not complete the form correctly, my bad.  I gave him an 'A' thinking that would trigger him getting the 500 points.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40472440
Andrew should have the points - see above.
0

Featured Post

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question