# Posts by 1legin

• ## countif using input cell as range depth

I got it using =COUNTIF(H2:INDIRECT(CONCATENATE("L",\$S\$3)),O3) thanks for pointing me in the right direction Ali

Nigel

:ole:

• ## countif using input cell as range depth

I have searched the net for help with INDIRECT function without any luck so far
Nigel

• ## countif using input cell as range depth

I have searched the net for help with INDIRECT function without any luck so far

Nigel

• ## countif using input cell as range depth

Lets say i have numbers in 5 columns H - M and the number to count in range is in AD3
is there a way to determin the range depth "\$M\$9" by using an input cell "AH3", so if i enter 100 in cell "AH3"
The formula will change from

To

Thanks
Nigel

• ## Pairs of numbers last out by date

I am trying to convert the formula in attached worksheet cell "P2" Draws tab to count Pairs, I dont know if this is possible with this formula,

Nigel

• ## Countifs?

Hi

Iam trying to find a formula to get the number of times a number appears on a given day, Please see below

Thanks

Nigel

• ## Trying to find pairs

Trying to get the formula for count of pairs in range and display in chart below.

Thanks

Nigel

RANGE CHART
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Image1.jpg","data-attachmentid":1203328}[/ATTACH]

• ## Add new cells in "range" Row

Trying to get this code to add a new row in excel via a button, not the full row, just the first 10 cells.
Can anyone amend the code for me please

Thanks

Nigel

Sorry for not adding Tag's

• ## Help with COUNTIFS

Works great Thanks Alan

• ## Help with COUNTIFS

Hi Alan,

Thanks for the answer to my question,How would i implement this ? as i am no good with VBA.

Thanks again

Nigel

• ## Help with COUNTIFS

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]B[/TD]
[TD="class: xl64, width: 64"]C[/TD]
[TD="class: xl64, width: 64"]D[/TD]
[TD="class: xl64, width: 64"]E[/TD]
[TD="class: xl64, width: 64"]F[/TD]
[TD="class: xl64, width: 31"]G[/TD]
[TD="class: xl64, width: 64"]H[/TD]
[TD="class: xl64, width: 64"]I[/TD]

[/tr]

[tr]

[TD="class: xl64"]Row No.[/TD]
[TD="class: xl64"]N1[/TD]
[TD="class: xl64"]N2[/TD]
[TD="class: xl64"]N3[/TD]
[TD="class: xl64"]N4[/TD]
[TD="class: xl64"]N5[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]No.'s[/TD]
[TD="class: xl64"]Enter No.[/TD]

[/tr]

[tr]

[TD="class: xl63"]1[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]17[/TD]
[TD="class: xl63"]34[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]2[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]21[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl63"]34[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]14[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]22[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]4[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]20[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]5[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]16[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]21[/TD]
[TD="class: xl63"]36[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]6[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]36[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl63"]38[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]7[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]28[/TD]
[TD="class: xl63"]35[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]8[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl63"]31[/TD]
[TD="class: xl63"]32[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]9[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]34[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]10[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl63"]33[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]11[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]25[/TD]
[TD="class: xl63"]32[/TD]
[TD="class: xl63"]38[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]12[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]22[/TD]
[TD="class: xl63"]26[/TD]
[TD="class: xl63"]31[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]13[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]33[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl64"]
[/TD]

[/tr]

[/TABLE]
Hi all,

I am trying to work out the frequancy for numbers in columns B to F.
So there is a total of 500 rows "B1:F500", i would like the answers to be in column I, but
instead of having results for all rows, i would like to enter a number in cell "Enter No."
and have the results for that many rows.
Not sure if this is possible with countifs or drop down box etc ..

Thanks

Nigel

• ## Count since last out

That's Fantastic Ali, works great.

Thanks for the help

Nigel

• ## Count since last out

I have attached file for you to take a look at [ATTACH]n1202267[/ATTACH]

• ## Count since last out

I am trying to work out the interval between apperences of a number in a lottery game [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 240"]

[tr]

[TD="class: xl65, width: 64"] N1[/TD]
[TD="class: xl65, width: 64"]N2[/TD]
[TD="class: xl65, width: 64"]N3[/TD]
[TD="class: xl65, width: 64"]N4[/TD]
[TD="class: xl65, width: 64"]N5[/TD]

[/tr]

[tr]

[TD="class: xl66"]18[/TD]
[TD="class: xl67"]19[/TD]
[TD="class: xl68"]29[/TD]
[TD="class: xl69"]33[/TD]
[TD="class: xl70"]37[/TD]

[/tr]

[tr]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]32[/TD]
[TD="class: xl65"]38[/TD]

[/tr]

[tr]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]31[/TD]

[/tr]

[tr]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]19[/TD]
[TD="class: xl69"]33[/TD]
[TD="class: xl70"]37[/TD]

[/tr]

[tr]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]36[/TD]

[/tr]

[tr]

[TD="class: xl65"]16[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl65"]28[/TD]
[TD="class: xl65"]34[/TD]
[TD="class: xl65"]36[/TD]

[/tr]

[tr]

[TD="class: xl65"]14[/TD]
[TD="class: xl67"]19[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl69"]33[/TD]
[TD="class: xl70"]37[/TD]

[/tr]

[tr]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]23[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl68"]29[/TD]

[/tr]

[/TABLE]

Using just the top row for this example
No. 18 last appered 5 games ago
No. 19 last appered 3 games ago and then 6 games ago
No. 29 last appered 7 games ago
No. 33 last appered 3 games ago and then 6 games ago
No. 37 last appered 3 games ago and then 6 games ago

I am hoping to show answers in a columns " 1st time out, 2nd time out" etc...

Hope you can understand what i am trying to explain

Thanks

Nigel