Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
I've got this:
=COUNTIF(SWABS!F7:F237,"=*Station 1*") How can I add =COUNTIF(SWABS!L7:L237,"=*F*") to this to make it all work? In other words I want to count the number of "Station 1" values and the number of "F" corresponding values. THANKS! -- www.Marzetti.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
=SUMPRODUCT(--(ISNUMBER(FIND("Station
1",SWABS!F7:F237))),--(ISNUMBER(FIND("F",,SWABS!L7:L237)))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JohnLute" wrote in message ... I've got this: =COUNTIF(SWABS!F7:F237,"=*Station 1*") How can I add =COUNTIF(SWABS!L7:L237,"=*F*") to this to make it all work? In other words I want to count the number of "Station 1" values and the number of "F" corresponding values. THANKS! -- www.Marzetti.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
"Bob Phillips" wrote...
=SUMPRODUCT(--(ISNUMBER(FIND("Station 1",SWABS!F7:F237))), --(ISNUMBER(FIND("F",,SWABS!L7:L237)))) .... Note the ,, typo. Or use just one ISNUMBER call. =SUMPRODUCT(--ISNUMBER(FIND("Station 1",SWABS!F7:F237) +FIND("F",SWABS!L7:L237))) or, if the OP could suffer an array formula, =COUNT(FIND("Station 1",SWABS!F7:F237)+FIND("F",SWABS!L7:L237)) No magic in using +. - * / would work equally well. ^ might overflow. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
Thanks, Bob! That's not returning what's expected. It's returning "0" when
there's actually 2 "F's". In other words for all of the "Station 1" entries (8 total) there are only 2 that have "F's". I'm not sure what needs changed...? -- www.Marzetti.com "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("Station 1",SWABS!F7:F237))),--(ISNUMBER(FIND("F",,SWABS!L7:L237)))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JohnLute" wrote in message ... I've got this: =COUNTIF(SWABS!F7:F237,"=*Station 1*") How can I add =COUNTIF(SWABS!L7:L237,"=*F*") to this to make it all work? In other words I want to count the number of "Station 1" values and the number of "F" corresponding values. THANKS! -- www.Marzetti.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
"JohnLute" wrote in message
... I've got this: =COUNTIF(SWABS!F7:F237,"=*Station 1*") How can I add =COUNTIF(SWABS!L7:L237,"=*F*") to this to make it all work? In other words I want to count the number of "Station 1" values and the number of "F" corresponding values. THANKS! -- www.Marzetti.com If you need to count either those that include "Station 1" or those that include "F", then: =SUMPRODUCT(COUNTIF(SWABS!F7:F237,{"*Station 1*","*F*"})) -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: http://mvp.support.microsoft.com/profile/Kirill |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
Thanks very much, Harlan!
-- www.Marzetti.com "Harlan Grove" wrote: "Bob Phillips" wrote... =SUMPRODUCT(--(ISNUMBER(FIND("Station 1",SWABS!F7:F237))), --(ISNUMBER(FIND("F",,SWABS!L7:L237)))) .... Note the ,, typo. Or use just one ISNUMBER call. =SUMPRODUCT(--ISNUMBER(FIND("Station 1",SWABS!F7:F237) +FIND("F",SWABS!L7:L237))) or, if the OP could suffer an array formula, =COUNT(FIND("Station 1",SWABS!F7:F237)+FIND("F",SWABS!L7:L237)) No magic in using +. - * / would work equally well. ^ might overflow. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF quandry
Upps! I guess I misunderstood the question...
"KL" wrote If you need to count either those that include "Station 1" or those that include "F", then: =SUMPRODUCT(COUNTIF(SWABS!F7:F237,{"*Station 1*","*F*"})) KL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multivariable Data Spreadsheet Quandry | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Macro Quandry | Excel Discussion (Misc queries) | |||
macro quandry.....?? | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |