Solved

How to create formulas on the fly in Excel 2010

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 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