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

  • Thread starter Thread starter L. Howard
  • Start date Start date
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
 
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
Click to expand...

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

--
Click to expand...

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.
Click to expand...

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


Regards
Claus B.
 
Hi Howard,



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






try:

=COUNTIF(Sheet1!C12:M16,"P "&A4&"*")
Click to expand...

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
Click to expand...

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:49:37 -0800 (PST) schrieb L. Howard:
The usual suspect to cause the error in this case was ME. Misspelled sheet name.
Click to expand...

no matter, such things can happen.

In SkyDrive now are 4 formulas to count substrings


Regards
Claus B.
 
Back
Top