Countif Question

[MarkAndrews]

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
	A	B	C	D	E	F	G	H
1	FeeEarner	Level	REMorSAP		Actual	AuthactYes	AuthactNo	CapacityVariance
2	MarkAndrews	3	REM		0	80	-80	#DIV/0!
Sheet2

 

[Richard Schollar]

Mark

Do you mean:

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

or maybe:

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

Richard

 

[MarkAndrews]

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]

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

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

Richard

 

[MarkAndrews]

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]

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]

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
	A	B	C	D	E	F	G	H	I
1	FeeEarner	Level	REMorSAP		Capacity	Actual	AuthactYes	AuthactNo	CapacityVariance
2	MarkAndrews	3	S&P			0	80	-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]

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]

Fantastic, thanks as ever Richard

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

 

[MarkAndrews]

Fee Earner Capacity Plan.xls
	A	B	C	D	E	F	G	H	I
1	FeeEarner	Level	REMorSAP	Location	Capacity	Actual	AuthactYes	AuthactNo	CapacityVariance
2	MarkAndrews	3	S&P	BRD	0	3	80	-77	-2567%
3						4
4						4
5						4
6						4
7						4
8						4
Results