# SUMPRODUCT formula I think w/ wild card & number vs number as text

L

#### L. Howard

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

C

#### Claus Busch

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\$200,"P")
or
=SUMPRODUCT(--(Sheet1!\$D\$12:\$D\$200=A4),--(Sheet1!\$C\$12:\$C\$200="P"))

Regards
Claus B.

L

#### L. Howard

Hi Howard,

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

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\$200,"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

C

#### Claus Busch

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.

L

#### L. Howard

Hi Howard,

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

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

L

#### L. Howard

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

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

L

#### L. Howard

Hi Howard,

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

have a look:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

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

C

#### Claus Busch

Hi Howard,

Am Tue, 18 Feb 2014 07:49:37 -0800 (PST) schrieb L. Howard:
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.