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

[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

 

[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. 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

 

[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. 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. 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

 

[Claus Busch]

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=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "CountIf"


Regards
Claus B.

 

[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

 

[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.