SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
SUMPRODUCT formula I think w/ wild card & number vs number as text
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 |
All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com