Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif maybe
I have this formula in columns of cells in 'sheet A'! D column, and the
result is various numbers, e.g. 1 thru 10 =IF(COUNTIF(MasterTab!$BH$21:$BH$917,G13)0,COUNTI F(MasterTab!$BH$21:$BH$917,G13),"") On the master tab, I also have column B that has various codes in some of the cells. And also, on the master tab, in column E, have numbers that may match the G13 in the above formula. I need a formula in the A column of sheet A, that has to count the number of times the letter u shows up in the 'master Tab'!B column when the 'master tab'!E column matches G13. If g13 in the above formula =158 So in the below, the result should be 3, which is the first um for 158, the second um for 158, and the last uc for 158. 3 times the u matches the 158's. B E uc 144 um 158 um 158 um 144 uc 158 I hope this makes sense ? Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif maybe
Hi Steve
Try the below =SUMPRODUCT((ISNUMBER(SEARCH("u",B1:B100))*(E1:E10 0=G13))) If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: I have this formula in columns of cells in 'sheet A'! D column, and the result is various numbers, e.g. 1 thru 10 =IF(COUNTIF(MasterTab!$BH$21:$BH$917,G13)0,COUNTI F(MasterTab!$BH$21:$BH$917,G13),"") On the master tab, I also have column B that has various codes in some of the cells. And also, on the master tab, in column E, have numbers that may match the G13 in the above formula. I need a formula in the A column of sheet A, that has to count the number of times the letter u shows up in the 'master Tab'!B column when the 'master tab'!E column matches G13. If g13 in the above formula =158 So in the below, the result should be 3, which is the first um for 158, the second um for 158, and the last uc for 158. 3 times the u matches the 158's. B E uc 144 um 158 um 158 um 144 uc 158 I hope this makes sense ? Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif maybe
Thank you,
It's working correctly for what I asked, but I left off another criterea. Her'e the working formula. =SUMPRODUCT((ISNUMBER(SEARCH("u",MasterTab!$B$21:$ B$917))*(MasterTab!$E$21:$E$917=G13))) It produces 13, which is how many u's are matching to G13. What I forgot is .... This formula is in Mastertab!BH column. =IF('EO-WO'!$D$2=BC22,BF22,"") It produces either the numbers that may match in Mastertab! E column, or "". There are only 4 u's that have a number in BH as per the above, that also have the 'u' and the match to G13. So the as shown is correctly producing 13 u's that match G13, but only 4 of those 13's also have the g13 number in the BH column. I need that additional critera result of 4. I hope this makes sense, and would understand your frustration with my probable poor explanation. Thanks again, Steve "Jacob Skaria" wrote: Hi Steve Try the below =SUMPRODUCT((ISNUMBER(SEARCH("u",B1:B100))*(E1:E10 0=G13))) If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: I have this formula in columns of cells in 'sheet A'! D column, and the result is various numbers, e.g. 1 thru 10 =IF(COUNTIF(MasterTab!$BH$21:$BH$917,G13)0,COUNTI F(MasterTab!$BH$21:$BH$917,G13),"") On the master tab, I also have column B that has various codes in some of the cells. And also, on the master tab, in column E, have numbers that may match the G13 in the above formula. I need a formula in the A column of sheet A, that has to count the number of times the letter u shows up in the 'master Tab'!B column when the 'master tab'!E column matches G13. If g13 in the above formula =158 So in the below, the result should be 3, which is the first um for 158, the second um for 158, and the last uc for 158. 3 times the u matches the 158's. B E uc 144 um 158 um 158 um 144 uc 158 I hope this makes sense ? Thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif maybe
=SUMPRODUCT((ISNUMBER(SEARCH("u",MasterTab!$B$21:$ B$917))*
(MasterTab!$E$21:$E$917=G13)* (MasterTab!$BH$21:$BH$917=G13))) If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Thank you, It's working correctly for what I asked, but I left off another criterea. Her'e the working formula. =SUMPRODUCT((ISNUMBER(SEARCH("u",MasterTab!$B$21:$ B$917))*(MasterTab!$E$21:$E$917=G13))) It produces 13, which is how many u's are matching to G13. What I forgot is .... This formula is in Mastertab!BH column. =IF('EO-WO'!$D$2=BC22,BF22,"") It produces either the numbers that may match in Mastertab! E column, or "". There are only 4 u's that have a number in BH as per the above, that also have the 'u' and the match to G13. So the as shown is correctly producing 13 u's that match G13, but only 4 of those 13's also have the g13 number in the BH column. I need that additional critera result of 4. I hope this makes sense, and would understand your frustration with my probable poor explanation. Thanks again, Steve "Jacob Skaria" wrote: Hi Steve Try the below =SUMPRODUCT((ISNUMBER(SEARCH("u",B1:B100))*(E1:E10 0=G13))) If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: I have this formula in columns of cells in 'sheet A'! D column, and the result is various numbers, e.g. 1 thru 10 =IF(COUNTIF(MasterTab!$BH$21:$BH$917,G13)0,COUNTI F(MasterTab!$BH$21:$BH$917,G13),"") On the master tab, I also have column B that has various codes in some of the cells. And also, on the master tab, in column E, have numbers that may match the G13 in the above formula. I need a formula in the A column of sheet A, that has to count the number of times the letter u shows up in the 'master Tab'!B column when the 'master tab'!E column matches G13. If g13 in the above formula =158 So in the below, the result should be 3, which is the first um for 158, the second um for 158, and the last uc for 158. 3 times the u matches the 158's. B E uc 144 um 158 um 158 um 144 uc 158 I hope this makes sense ? Thanks, Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif maybe
Peferct. It works great. Thank you, and thanks especially for your patience.
Steve "Jacob Skaria" wrote: =SUMPRODUCT((ISNUMBER(SEARCH("u",MasterTab!$B$21:$ B$917))* (MasterTab!$E$21:$E$917=G13)* (MasterTab!$BH$21:$BH$917=G13))) If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Thank you, It's working correctly for what I asked, but I left off another criterea. Her'e the working formula. =SUMPRODUCT((ISNUMBER(SEARCH("u",MasterTab!$B$21:$ B$917))*(MasterTab!$E$21:$E$917=G13))) It produces 13, which is how many u's are matching to G13. What I forgot is .... This formula is in Mastertab!BH column. =IF('EO-WO'!$D$2=BC22,BF22,"") It produces either the numbers that may match in Mastertab! E column, or "". There are only 4 u's that have a number in BH as per the above, that also have the 'u' and the match to G13. So the as shown is correctly producing 13 u's that match G13, but only 4 of those 13's also have the g13 number in the BH column. I need that additional critera result of 4. I hope this makes sense, and would understand your frustration with my probable poor explanation. Thanks again, Steve "Jacob Skaria" wrote: Hi Steve Try the below =SUMPRODUCT((ISNUMBER(SEARCH("u",B1:B100))*(E1:E10 0=G13))) If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: I have this formula in columns of cells in 'sheet A'! D column, and the result is various numbers, e.g. 1 thru 10 =IF(COUNTIF(MasterTab!$BH$21:$BH$917,G13)0,COUNTI F(MasterTab!$BH$21:$BH$917,G13),"") On the master tab, I also have column B that has various codes in some of the cells. And also, on the master tab, in column E, have numbers that may match the G13 in the above formula. I need a formula in the A column of sheet A, that has to count the number of times the letter u shows up in the 'master Tab'!B column when the 'master tab'!E column matches G13. If g13 in the above formula =158 So in the below, the result should be 3, which is the first um for 158, the second um for 158, and the last uc for 158. 3 times the u matches the 158's. B E uc 144 um 158 um 158 um 144 uc 158 I hope this makes sense ? Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Use | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |