# Question on using COUNTIF() formula

#### Amanda80

##### New Member
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?

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You still need to state the full range where to search for 4305...

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
ABCD
174
18
19
204305AV
2143054308AV
2243064305AV
234307
244305AV
Sheet1

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?

That's weird, it works over here
Book1
BCDEFGH
66
74305AV
8AV
94305AV
104305AV
11AV
124305AV
134305AV
144305AV
Oct 1-14

Hey,

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

Hey,

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

Replies
13
Views
585
Replies
9
Views
307
Replies
4
Views
310
Replies
2
Views
326
Replies
7
Views
208

1,219,811
Messages
6,150,361
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back