vba code for count restart formula if condition

[pedersenn]

Hi

does anyone want to help me with a vba code /function?

i need a formula to restarrt count if condition is meet on a specified column on same row with condition

A5=cond
A8=cond


=countif($B$1:$E1,B1)
when drag down the formula it in
=countif($B$1:$E2,B2)
=countif($B$1:$E3,B3)
=countif($B$1:$E4,B4)

=countif($B$5:$E5,B5) the formula has changed due to the condition in A5
=countif($B$5:$E6,B6)
=countif($B$5:$E7,B7)
=countif($B$8:$E8,B8) the formula has changed due to the condition in A8

[Kaper]

you probably do it in some loop, so in the loop

'check your condition, if true note row in a variable (say i)
i = currentrow
'else not change i value
'and set formula with
yourcell.formular1c1="=countif(R" & i & "C2:R" & i & "C5,RC2)"

[Kaper]

Assuming you want to have such formulas in F1:F10 and your condition is cell in column A>100

dim i as long, j as long
i = 1
for j = 1 to 10
  if cells(j,"A")>100 then i = j
  cells(j,"F").formular1c1="=countif(R" & i & "C2:R" & i & "C5,RC2)"
next j

[pedersenn]

can you make it a function to be insertable from formulas menu?

[pedersenn]

can you make it a function to be insertable from formulas menu?

[Pauleyb]

A few ways to skin this cat.
Avoiding macros and using a dynamic range...
I used column F as my 'change detector'. In my case, I put a 1 in the cell if 'the change occurred'. Note that the first cell in the column F range needs to be a 1.

Next, with cell H1 selected I went to Name Manager and created this
Name: myrng
Formula: =OFFSET($B$1,MAX(ROW($F$1:$F1)*($F$1:$F1))-1,0,ROW()-MAX(ROW($F$1:$F1)*($F$1:$F1))+1,4)

Then in H1:
=countif(myrng, b1)

[Pauleyb]

@pedersenn - who are you talking to?

@Kaper's solution is vba subroutine and is not a function
My solution is a function that you can put in the formulas menu (it is countif, just like yours), but is using a dynamic named range.
If you are requesting a user defined function, I'd have to wonder why, since you have two viable solutions already.

[pedersenn]

I need a function becase I do not know how to use long formulas.

please upload a file with your example.

[Kaper]

Attached file contains different formulas than these from post #1, so code has to be changed accordingly:

Sub test()
Dim i As Long, j As Long
i = 3
For j = 3 To Cells(Rows.Count, "B").End(xlUp).Row
  If Cells(j, "A") = "cond" Then i = j
  Cells(j, "C").FormulaR1C1 = "=countif(R" & i & "C2:RC2,RC2)"
Next j
End Sub

Once you run it appropriate range will be filled with formulas. delete contents of column C and run the macro.



If what you look for is UDF (vba to be used the same way you use regular formula) here is the sample code and usage is presented in attached file:

Function countif_from_cond(input_range As Range, counted As Variant, cond As Variant) As Long
Dim myarr, i As Long, j As Long
myarr = input_range.Value
i = UBound(myarr)
Do
  If myarr(i, 2) = counted Then j = j + 1
  i = i - 1
Loop Until myarr(i + 1, 1) = cond Or i < 0
countif_from_cond = j
End Function

it is written just as regular formula in C3 and copied down.

[pedersenn]

the code works for just for a column

why can't drag to the right?

[pedersenn]

i tried UDF code but i got message
"The function take no argument"

[Kaper]

As for "The function take no argument" - see example attached to post #9
As no right drag - your (one column) example was used. May be you shall change some relative references to absolute or mixed.

[pedersenn]

in file you can see how results looks like.

[Kaper]

UDF modified to work with multicolumn range:

Function countif_from_cond(input_range As Range, counted As Variant, cond As Variant) As Long
Dim myarr, i As Long, j As Long, k As Long, colno As Long
myarr = input_range.Value
colno = UBound(myarr, 2)
i = UBound(myarr)
Do
  For k = 2 To colno
    If myarr(i, k) = counted Then j = j + 1
  Next k
  i = i - 1
Loop Until myarr(i + 1, 1) = cond Or i <= 0
countif_from_cond = j
End Function

usage (adressing to allow right-dragging) in E3:

=countif_from_cond($A$3:$C3,B3,1)

[pedersenn]

Thank you Mr. Kaper

But there are some things to adjust.
The formula works almost perfectly
but if there are other columns with data between the column and the other data columns, it no longer works.

Another minor problem is that when I insert the formula it displays the range incorrectly

column A is reserved for the condition, so it should not be displayed as part of the range.

The counting range is $B$3:C3 not $A$3:C3

please include the option to ask for column with condition that if there are other columns with data between the
columns numbered and the column of data make it does not alter the result.


Thank you

[Kaper]

First column of input_range As Range (so Column A) is used only to check condition. It is not used in "countif"
Note:

  For k = 2 To colno

or before (post #9)

  If myarr(i, 2) = counted Then j = j + 1

If counting would include column A, this should be

for k=1 to ...

or

If myarr(i, 1) = counted Then j = j + 1

If you want to use it with different data layout my suggestion is to rearrange data.

[pedersenn]

i cannot rearange data pls change code

[Kaper]

It is my final approach. (again there was no attachment reflecting REAL data layout :-( I believe it is fair if you put more effort and answering person donates knowledge).

If it works - perfect (not tested as there was no attachment with such data layout).
If not - my suggestion is to try: https://www.excelforum.com/payments.php

Note that it's user responsibility to use proper ranges (for instance same row number in condition_range and input_range).

Function countif_from_cond(input_range As Range, cond_range as Range, counted As Variant, cond As Variant) As Long
Dim myarr, condarr, i As Long, j As Long, k As Long, colno As Long
myarr = input_range.Value
condarr = cond_range.Value
colno = UBound(myarr, 2)
i = UBound(myarr)
Do
  For k = 1 To colno
    If myarr(i, k) = counted Then j = j + 1
  Next k
  i = i - 1
Loop Until condarr(i + 1, 1) = cond Or i <= 0
countif_from_cond = j
End Function

sample usage:

=countif_from_cond($B$3:$C3,$A$3:$A3,B3,1)

values in columns B:C, cond in column A