# Countif Question

#### MarkAndrews

##### Well-known Member
I have the following data table which is set to be filled in today

However - I would like to impliment this countif

=COUNTIF('Paste IQ Report Here'!A:A,"a1*")

The problem lies - "a1*"

I know Countif can be modified to look for certain words, which is what i'm after, the certain word exists in A1 (a pre entered list of names)

Is there a way to adapt?

TIA
Book1
ABCDEFGH
1FeeEarnerLevelREMorSAPActualAuthactYesAuthactNoCapacityVariance
2MarkAndrews3REM080-80#DIV/0!
Sheet2

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Richard Schollar

##### MrExcel MVP
Mark

Do you mean:

=COUNTIF('Paste IQ Report Here'!A:A,A1)

or maybe:

=COUNTIF('Paste IQ Report Here'!A:A,A1&"*")

Richard

#### MarkAndrews

##### Well-known Member
Mark

Do you mean:

=COUNTIF('Paste IQ Report Here'!A:A,A1)

or maybe:

=COUNTIF('Paste IQ Report Here'!A:A,A1&"*")

Richard

Will either of these pick out the text in A1, down the column of "Paste IQ Report Here"

?

#### Richard Schollar

##### MrExcel MVP
If you only have a partial match, then you'll want to use:

=COUNTIF('Paste IQ Report Here'!A:A,"*" & \$A\$1 & "*")

Richard

#### MarkAndrews

##### Well-known Member
If you only have a partial match, then you'll want to use:

=COUNTIF('Paste IQ Report Here'!A:A,"*" & \$A\$1 & "*")

Richard

I have on "Paste IQ Report Here" in Cell A1

Mark Andrews was here

It's not counting my name?

#### Richard Schollar

##### MrExcel MVP
Sorry Mark you've got me totally confused:

what do you have in A1 that is the Countif criteria?

What do you have in 'Paste IQ Report Here'!A:A - please give a sample of the data.

Richard

#### MarkAndrews

##### Well-known Member
Sorry Mark you've got me totally confused:

what do you have in A1 that is the Countif criteria?

What do you have in 'Paste IQ Report Here'!A:A - please give a sample of the data.

Richard

Sorry Richard

Results sheet looks like this -
Book1
ABCDEFGHI
1FeeEarnerLevelREMorSAPCapacityActualAuthactYesAuthactNoCapacityVariance
2MarkAndrews3S&P080-80#DIV/0!
Results

Paste IQ Report Here sheet is totall blank, however column A descending will contain names (pre entered) which will need to be counted from "Paste IQ Report Here" = column A

Hope this makes more sense. The names which will appear in "Paste IQ Report Here" sometimes have extra information included in the cell, therefore i just need the names counted to give me a volume figure which will go in "Results" column F

#### Richard Schollar

##### MrExcel MVP
So you actually want to count the number of occurences in "Paste IQ Report Here" of the name in A2 on the Results sheet?

=COUNTIF('Paste IQ Report Here'!A:A,"*" & Results!A2 & "*")

If I have understood correctly, then this works for me.

Richard

#### MarkAndrews

##### Well-known Member
Fantastic, thanks as ever Richard

Can i simply copy this down, as it seems to be adding itself to the cell above?

#### MarkAndrews

##### Well-known Member
Fee Earner Capacity Plan.xls
ABCDEFGHI
1FeeEarnerLevelREMorSAPLocationCapacityActualAuthactYesAuthactNoCapacityVariance
2MarkAndrews3S&PBRD0380-77-2567%
34
44
54
64
74
84
Results

Replies
3
Views
860
Replies
7
Views
271
Replies
9
Views
303
Replies
0
Views
205
Replies
0
Views
258

1,171,976
Messages
5,878,586
Members
433,354
Latest member
miihj

### 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