Excel help needed

[Excellence]

Good day. I need help regarding the Countif function. The problem is: the range parameter is variable. I will try to put an example in order to make it easier to understand.
On column A we have 10 rows from 1 to 10. In A1 we have the value 2, so as in the cell A4 and A10. In cell A2 we have the value 3, in A5 the value 4.
What I want is: calculate how many times the value 2 appeared in the range that contains 4 values, starting from row 1 (here is the range A1:A5). I also want to have the possibility to change the number of values contained, like instead of 4 to have 5values. My first thought was to create another column that would count the values in column A row by row and use a match function in combination whit address but it did not work. The problem is even more difficult since this range is on a different sheet. Any ideas?

 

[Excellence]

I think I resolved the problem, I made another tabels that would help me.

 

[Patrick123]

Hi Excellence,
even though you've sorted out the problem.
Countif is relatively simple,
=Countif(Range,Criteria)
The easiest way to get it working, is to click on the Fx button up top.
This will then allow you to specify the range, as it's on another worksheet, you select that work sheet, then the range. If you are presetting the criteria, then type in the criteria (2 in your example) or select a cell that would have the criteria.
This example COUNTIF(Sheet2!D1: D10,M2) (I had to put a space there otherwise a smile shows)

would look at the Sheet 2 range d1 to d10. to lock the range, don't forget to add the range as $D$1:$D$10

Regards
Patrick

 

[Excellence]

Thank you for the quick response Patrick. But this is not the answer of the problem. Your range is D110, or in my example A1:A10. But A1:A10 contains 5 values. I need the range that contains only 4 values, and I needed this range to change automatic. Let’s say we would like to count how many times 2 appeared in 5 values. In our example the range would be A1:A10, but if we write in cell a3 the value 5 the range that contains 5 values would be A1:A5. Hope this is more clear but again I kind of solved it (reached column IG!!).

 

[Excellence]

D1 : D10, not that smiley...

 

[Patrick123]

Yep, now I understand you question, you needed the range to vary. As you've done, either a choice of a duplicate table or you need a VB macro.

Regards
Patrick

 

[Excellence]

Good day. I have one more question related to the Countif function. How can I make the criteria variable? What I need is to count how many values are larger or smaller from a certain range compared to the value contain by another cell. E.g.: =Countif(A1: A3;>A1). I don’t want to write the value contained by A1. I believe Vb module is needed but I only know the If Then and Case Is structures that are useless in this case.

 

[GillesD]

Counting values

Excel offers various possibilities when it comes to counting frequency of number(s) in a range.

The function COUNTIF is often the easy way to do it. The formula =COUNTIF(A1:A3;">"&A1) will work although I find it odd to include the cell with the value you want to compare in the range to look for. I would have expected a circular reference but it is not the case.

Another possibility is the FREQUENCY function.

Also, regarding Patrick123 question, when the range may vary, the OFFSET function often can be used with success.

 

[Excellence]

Wow. Thank you very much GillesD.