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

Please Login or Register  to view this content.

[Kaper]

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

Please Login or Register  to view this content.

[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)

[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]

@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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

or before (post #9)

Please Login or Register  to view this content.

If counting would include column A, this should be

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

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).

Please Login or Register  to view this content.

sample usage:

Please Login or Register  to view this content.

values in columns B:C, cond in column A