Combine a Countif with an Offset

[Caveman1964]

I have tried this every way I could.....I can't get to work but they work seperately.
=countif('Data Collection'!$d$7:$D:$2000,"GA")
=offset('data Collection'!$D$6,1,,2000)

D6 is the header. the data begins D7, but when I insert new row, I lose my data for my chart. The formula moves with it. But the two up above work but I can't get them to work together.
Been working on this for hours, please help.
Any help appreciated.

 

[RasGhul]

Hi Caveman,

It's not clear what you're trying to achieve with these functions, can you post some sample data showing what you want the end result to be?

 

[Caveman1964]

Sure!
I hope this helps. I don't know much what I am doing but trying to learn.
This is not working well trying to put in this forum.....I am sure there is a better way to do it.
I need one sheet to count the number of times GA shows up in column "Location" which is D beginning at row 7......on sheet 3.
However, I enter new info by inserting into row 7 new info where a new location shows up which will be one of the 4 shown. But my countif on sheet 4 changes and starts to count from row 8 of sheet 2 instead of 7. Every time I enter new, the countif changes down again. I need it to continue to count from D7 sheet two.
I know I am not explaining this well. but I sure hope you can see what I am trying to do.
Thank You

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]JOB[/TD]
[TD]location[/TD]
[TD][/TD]
[TD][/TD]
[TD]different sheet[/TD]
[TD]location[/TD]
[TD]amount of jobs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]GA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GA[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7827852[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AL[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]785272[/TD]
[TD]GA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FL[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]389373[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SC[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]373872[/TD]
[TD]SC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Caveman,

You could use this dynamic named range with the offset function below and plug that into your countif if.

Note how the offset formula starts before D7 where you are inserting your new row, thus expanding the countif;

Book1
	C	D
6	JOB	location
7	123456	AL
8	789456	AL
9	589634	GA
10	123456	GA
11	7827852	AL
12	785272	GA
13	389373	FL
14	373872	SC
15		GA
16		SC
Sheet3

Book1
	A	B
1	location	amount of jobs
2	GA	4
3	AL	3
4	FL	1
5	SC	2
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIF(location,Sheet2!A2)

Named Ranges
Name	Refers To	Cells
location	=OFFSET(Sheet3!$D$6,,,COUNTA(Sheet3!$D:$D))

 

[Caveman1964]

Wow! That worked great!
Thank You RasGhul! I appreciate your help .

 

[RasGhul]

you're welcome