Change range by condition

[pedersenn]

Hi

how can i change a range in a countif formula by condition

if A5=1

at b1 is formula =countif($B$1:B100,5)

when formula comes at B5 i need formula change as = countif($B$5:B100,5)

[AliGW]

What is 'counting' meant to be? There is no such function. Please provide some sample data with expected outcomes.

[Sam Capricci]

Your question is somewhat vague. for this part

at b1 is formula =countif($B$1:B100,5)

when formula comes at B5 i need formula change as = countif($B$5:B100,5)

you could simply change this =countif($B$1:B100,5) to this =countif(B1:$B$100,5) then when you copy and past it in cell B5 it will automatically become =countif(B5:$B$100,5) but I don't understand the first part where you are asking if A5 = 1, what does that have to do with changing the countif formula?

[pedersenn]

yes i can change it manual but that must be changed many times (hundreds times)

A5=1

A25=1

A50=1


Start formula is =countif($B$1:B1,criteria)
when formula comes at a5 or A25 etc the range must change in B$5$:B5,criteria
$B$25:B25,criteria and =countif($B$50:B50,criteria).

[Sam Capricci]

You don't have to change it many times, you only have to change it once. when you have a $ sign before the column ($B for example) it locks in the column. When you have the $ before the row number (B$1) it locks in the row. $B$1 locks in the cell. So in your original formula =COUNTIF($B$1:B100,5) you are locking in cell B1 but advancing B100. So if you copy and paste the formula from A1 (or wherever it is) to A5 for example, the formula will change from this =COUNTIF($B$1:B100,5) to this =COUNTIF($B$1:B105,5) But if you want the formula to lock in B100 as the end of the count formula but advance from B1 to B5 to B25 to B50 etc, then I'm saying change it from =COUNTIF($B$1:B100,5) to =COUNTIF(B1:$B$100,5) then when you copy it to A5 or A25 it will look the way you want. [=COUNTIF(B5:$B$100,5) and [=COUNTIF(B25:$B$100,5)]. You won't have to do it manually hundreds of times. Change your original formula and then copy it to each location you want.

[pedersenn]

the range must be changed when comes at A5 , A25, A50 etc

if on column C is formula =countif($B1:B5,criteria)

when formula comes at A5 then range must be countif($B$5:B5,criteria)

when formula comes at A25 the range must be countif($B$25:B25,criteria)

[Sam Capricci]

when formula comes at A5 then range must be countif($B$5:B5,criteria)

when formula comes at A25 the range must be countif($B$25:B25,criteria)

you're counting only one cell?
apart from that, the first reference of your formula $B$5 is locking in that cell. If you change the formula to B5 without the $ every time you copy and past it to a new location it will index to that new location. So if you have in A1 the formula =COUNTIF(B1:B100,5) and you copy that and paste it into A5 it will index to become =COUNTIF(B5:B105,5) and if you paste it into A25 it will become =COUNTIF(B25:B125,5)
if you want it to only go to B100 then writing the original formula as =COUNTIF(B1:$B$100,5) will allow you to copy it from A1 to A50 and it will change from =COUNTIF(B1:$B$100,5) to =COUNTIF(B50:$B$100,5)
I don't know how else to explain it. If this is not what you want you may have to post a sample which represents your data and results expected.

[pedersenn]

see the file and paste a formula to make results like in column C

[Sam Capricci]

from what you posted I still cannot tell what you are trying to accomplish but I wrote my formula and pasted it in the column you marked as "your formula" across from each row where there was a "1" in column A to show how it "indexes" as it progresses.

[pedersenn]

the results must be as in column C. the formula must count result for every cell when drag cell down.
the start range must be at same row $B$5:B5,criteria or $B$10:B10,criteria etc

[Sam Capricci]

all you have to do is copy the formula from the column where you wrote "your formula" in column D to column C then copy down like I did in the attached.

[pedersenn]

the formula must bigin with =countif($B$5:B5,criteria)

the formula must be present in every cell on column E

[Sam Capricci]

if this isn't what you want then you'll need to upload a sheet with some expected results.
BTW what you wrote in this post appears to contradict what you wrote in the first post where you wanted to index the formula.

[pedersenn]

i uploaded the file


on column A are some 1's when i drag down the formula the row of
range must change as row where is 1.
1 is on row 10, row 15 and row 25

so range must change from $B$5:B5,3 to $B$10:B10,3 then $B$15:B15,3 and so on
you must find a complex formul using IF function or other functions.

[FlameRetired]

It appears that you need a dynamic range in first argument of COUNTIF.

BTW. Your profile says Excel 2007. You uploaded an *.xls file. I uploaded same in case this has to be compatible with earlier version.

The formula is in the file.

Please Login or Register  to view this content.

[pedersenn]

the formula goes wrong at E22 the result must be 2 becose are 2 of 3 number

[FlameRetired]

Nope.
According to the 5 row pattern you indicate a new range "anchor" starts at row 20. At row 22 the range is now $B20:B22. The values are {4;1;3}. There is one 3.

[AliGW]

Dave - I don't think it's every five rows - I think it's each time a 1 appears in column A that the formula needs to reset. Unfortunately, I haven't managed to work out how to adapt your formula for that.

[AliGW]

OK - you will need to add an extra 1 to cell A5 for this to work:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	cond	Data	formula	Formula change	Formula propusa
2
3
4
5	1	3	1	"=COUNTIF($B$5:B5,3)"	1
6		45	1	"=COUNTIF($B$5:B6,3)"	1
7		3	2	etc	2
8		23	2		2
9		46	2		2
10	1	4	0	"=COUNTIF($B$10:B10,3)"	0
11		7	0		0
12		5	0		0
13		2	0		0
14		3	1		1
15	1	55	0	"=COUNTIF($B$15:B15,3)"	0
16		7	0		0
17		5	0		0
18		4	0		0
19		3	1		1
20		4	1		1
21		1	1		1
22		3	2		2
23		9	2		2
24		76	2		2

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	E
5	=COUNTIF(INDIRECT(ADDRESS(LOOKUP(2,1/($A$5:A5=1),ROW($A$5:$A5)),2)&":"&ADDRESS(ROW(),2)),3)

Sheet: Sheet1

[AliGW]

Here's a version that will work WITHOUT having to add an extra 1 in A5:

=IF(COUNTIF(A$5:A5,1)<1,COUNTIF(B$5:B5,3),COUNTIF(INDIRECT(ADDRESS(LOOKUP(2,1/($A$5:A5=1),ROW($A$5:$A5)),2)&":"&ADDRESS(ROW(),2)),3))

[FlameRetired]

Dave - I don't think it's every five rows - I think it's each time a 1 appears in column A that the formula needs to reset. Unfortunately, I haven't managed to work out how to adapt your formula for that.

Hmmm. That could explain a lot!! I hadn't noticed that pattern. Thank you Ali.
I solved the wrong problem. I do that a lot!

[FlameRetired]

the range must be changed when comes at A5 , A25, A50 etc

if on column C is formula =countif($B1:B5,criteria)

when formula comes at A5 then range must be countif($B$5:B5,criteria)

when formula comes at A25 the range must be countif($B$25:B25,criteria)

This part is confusing.

Where do you want to start the ranges? I had assumed B5. Now I am not so sure of much.

[AliGW]

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FlameRetired]

An afterthought.

This one uses two helper columns.
In F5:F49

Please Login or Register  to view this content.

In G5:G49

Please Login or Register  to view this content.

Then in F5:F49 the counting formula.

Please Login or Register  to view this content.