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
you probably do it in some loop, so in the loop
Please Login or Register to view this content.
Best Regards,
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.
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)
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
can you make it a function to be insertable from formulas menu?
can you make it a function to be insertable from formulas menu?
@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.
I need a function becase I do not know how to use long formulas.
please upload a file with your example.
Last edited by pedersenn; 09-11-2017 at 05:08 PM.
Attached file contains different formulas than these from post #1, so code has to be changed accordingly:
Once you run it appropriate range will be filled with formulas. delete contents of column C and run the macro.Please Login or Register to view this content.
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:
it is written just as regular formula in C3 and copied down.Please Login or Register to view this content.
Last edited by Kaper; 09-12-2017 at 02:55 AM.
the code works for just for a column
why can't drag to the right?
Last edited by pedersenn; 09-12-2017 at 12:14 PM.
i tried UDF code but i got message
"The function take no argument"
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.
in file you can see how results looks like.
UDF modified to work with multicolumn range:
usage (adressing to allow right-dragging) in E3:Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
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
First column of input_range As Range (so Column A) is used only to check condition. It is not used in "countif"
Note:
or before (post #9)Please Login or Register to view this content.
If counting would include column A, this should bePlease Login or Register to view this content.orPlease Login or Register to view this content.If you want to use it with different data layout my suggestion is to rearrange data.Please Login or Register to view this content.
i cannot rearange data pls change code
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).
sample usage:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
values in columns B:C, cond in column A
Last edited by Kaper; 09-18-2017 at 02:32 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks