Countif Question

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
May 2, 2006
Messages
1,963
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
May 2, 2006
Messages
1,963
Fantastic, thanks as ever Richard :biggrin: :biggrin:

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

Forum statistics

Threads
1,136,370
Messages
5,675,367
Members
419,566
Latest member
moni2277

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top