Question on using COUNTIF() formula

[Amanda80]

I am trying to count how many times "AV" appears in a specific range in a seperate worksheet and have that counted value returned into my "roll-up sheet".

However, because the row #'s can vary due to me adding or deleting rows, my range is never the same twice. I would like to know if there is anyway to count the cells if it sees "AV" and know that I only want to count the range that has a certain number (4305) somewhere within the row?

Please help!

 

[Aladin Akyurek]

You still need to state the full range where to search for 4305...

 

[orbea_adam]

Aladin, correct me if I'm wrong...Amanda80, if you set up your range in the formula and then insert or delete rows within the range, then the formula should self-adjust. As for the 4305 criteria, here's my take

Book1
	A	B	C	D
17		4
18
19
20	4305		AV
21	4305	4308	AV
22	4306	4305	AV
23		4307
24		4305	AV
Sheet1

 

[Amanda80]

I'm going to attach my sheets for examples

Hopefully this will be easier, I'm not sure if I'm explaining what I am looking for clearly....

Here's the formula I entered and when I plugged this in, it did not add anything:

=SUMPRODUCT(--('Oct 1-14'!B7:B196=4305),--('Oct 1-14'!H7:H196="AV"))

Does this make sense?

 

[orbea_adam]

That's weird, it works over here

Book1
	B	C	D	E	F	G	H
6			6
7	4305						AV
8							AV
9	4305						AV
10	4305						AV
11							AV
12	4305						AV
13	4305						AV
14	4305						AV
Oct 1-14

 

[Amanda80]

more info on Monday

Hey,
Thanks for your help.

I'm going to post a copy of my worksheets for all to see on Monday. That might be easier than trying examples.

I'm on my way out the door now, so hopefully I'll still catch you on Monday....

PS-how do I post an example of the sheet?

Amanda

 

[orbea_adam]

Re: more info on Monday

Hey,
Thanks for your help.

I'm going to post a copy of my worksheets for all to see on Monday. That might be easier than trying examples.

I'm on my way out the door now, so hopefully I'll still catch you on Monday....

PS-how do I post an example of the sheet?

Amanda

Go here: http://www.puremis.net/excel/downloads.shtml and scroll down til: HtmlMaker2.42