Solved

# How to create formulas on the fly in Excel 2010

Posted on 2014-11-29
Medium Priority
181 Views
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
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
• 3

LVL 6

Accepted Solution

Asif Bacchus earned 2000 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)
``````
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

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

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:

0

Author Comment

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

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

## Featured Post

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
###### Suggested Courses
Course of the Month11 days, 3 hours left to enroll