Two sheets.

Sheet 2 cell A4 = 456 (a true number)

Sheet 1 Cells C12:M16 contain blank cells and cell values like:

P 456 J Smith
A 123 A Monk
P 789 I Seek
P 456 J Smith

So the numbers in this range will be text.

Formula on sheet 2 (cell D4) to count how many of the A4 values occur in the sheet 1 range that have a P in front of it.

D4 would show 2 in this mini example.

Thanks.
Howard

Hi Howard,

Am Tue, 18 Feb 2014 04:40:08 -0800 (PST) schrieb L. Howard:

Sheet 2 cell A4 = 456 (a true number)

Sheet 1 Cells C12:M16 contain blank cells and cell values like:

P 456 J Smith
A 123 A Monk
P 789 I Seek
P 456 J Smith

if "P" is in column C and the numbers are in column D then try:

=COUNTIFS(Sheet1!$D$12:$D$200,A4,Sheet1!$C$12:$C$2 00,"P")
or
=SUMPRODUCT(--(Sheet1!$D$12:$D$200=A4),--(Sheet1!$C$12:$C$200="P"))



Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, February 18, 2014 6:10:42 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Tue, 18 Feb 2014 04:40:08 -0800 (PST) schrieb L. Howard:



Sheet 2 cell A4 = 456 (a true number)



Sheet 1 Cells C12:M16 contain blank cells and cell values like:



P 456 J Smith

A 123 A Monk

P 789 I Seek

P 456 J Smith



if "P" is in column C and the numbers are in column D then try:



=COUNTIFS(Sheet1!$D$12:$D$200,A4,Sheet1!$C$12:$C$2 00,"P")

or

=SUMPRODUCT(--(Sheet1!$D$12:$D$200=A4),--(Sheet1!$C$12:$C$200="P"))







Regards

Claus B.

--

Hi Claus,

After seeing you response, I now see how misleading my query is.

This "P 456 J Smith" and the others like it are all in a cell to themselves.

So my little mini example would be four cells with in the C12:M16 range.

Hence my thought of needing the wild card trick and the need to compare a real number with a number as text.

Howard

Howard

Hi Howard,

Am Tue, 18 Feb 2014 06:29:12 -0800 (PST) schrieb L. Howard:

This "P 456 J Smith" and the others like it are all in a cell to themselves.

So my little mini example would be four cells with in the C12:M16 range.

try:
=COUNTIF(Sheet1!C12:M16,"P "&A4&"*")


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, February 18, 2014 6:37:58 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Tue, 18 Feb 2014 06:29:12 -0800 (PST) schrieb L. Howard:



This "P 456 J Smith" and the others like it are all in a cell to themselves.



So my little mini example would be four cells with in the C12:M16 range.



try:

=COUNTIF(Sheet1!C12:M16,"P "&A4&"*")


It is returning a #Value error with the green triangle. Plus a pop up window "Update values: Sheet 1".

I went to the lookup range and verified that there were no leading or trailing spaces in the target cells.

I'll try it out on a new unused worksheet.

Howard

I'll try it out on a new unused worksheet.



Howard

Okay, the formula works on a new sheet.

Thanks for the formula, I will set about to see what is going on with the sheet/s.

If there are some usual suspect you know of as to why it works on one sheet but not another I'd be interested.

Thanks, Claus.

Howard

Hi Howard,

Am Tue, 18 Feb 2014 07:20:10 -0800 (PST) schrieb L. Howard:

It is returning a #Value error with the green triangle. Plus a pop up window "Update values: Sheet 1".

have a look:
https://skydrive.live.com/#cid=9378A...121822A3%21326
for workbook "CountIf"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, February 18, 2014 7:29:03 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Tue, 18 Feb 2014 07:20:10 -0800 (PST) schrieb L. Howard:



It is returning a #Value error with the green triangle. Plus a pop up window "Update values: Sheet 1".



have a look:

https://skydrive.live.com/#cid=9378A...121822A3%21326

for workbook "CountIf"





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

The usual suspect to cause the error in this case was ME. Misspelled sheet name.

That trim formula could very well come in handy.

Thanks again.

Howard

Hi Howard,

Am Tue, 18 Feb 2014 07:49:37 -0800 (PST) schrieb L. Howard:

https://skydrive.live.com/#cid=9378A...121822A3%21326

for workbook "CountIf"
The usual suspect to cause the error in this case was ME. Misspelled sheet name.

no matter, such things can happen.

In SkyDrive now are 4 formulas to count substrings


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2