Solved

How to create formulas on the fly in Excel 2010

Posted on 2014-11-29
6
164 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
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 …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now