Solved

How to create formulas on the fly in Excel 2010

Posted on 2014-11-29
6
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

749 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