Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria with the COUNTIF function or somthing else?
I need a function that adds up a column of part of a string(ex. this_that,
and i just need to make sure "this" is in the string) and then another column also has to be equal to "Closed". I haven't been able to get anything to work, any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria with the COUNTIF function or somthing else?
Assume A is your first column (the one that has this_that) and B is your
second column (the one with Closed in it): =SUMPRODUCT(--ISNUMBER(SEARCH("this",A1:A10)),--(B1:B10="Closed")) Just modify that to the range your data is actually in. Does that help? -- Regards, Dave "Robert Brown" wrote: I need a function that adds up a column of part of a string(ex. this_that, and i just need to make sure "this" is in the string) and then another column also has to be equal to "Closed". I haven't been able to get anything to work, any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria with the COUNTIF function or somthing else?
IT almost worked, but with the information you gave me i was able to work it
out. ISNUMBER gives true/false and so does B1:B10="Closed". So instead of adding those together, multiply (true*true=1,true*false=0,ect.), so it would look like this : =SUMPRODUCT(ISNUMBER(SEARCH("this",A3:A30))*(J3:J3 0="closed")) This gave me what i needed So thank you for the help! "David Billigmeier" wrote: Assume A is your first column (the one that has this_that) and B is your second column (the one with Closed in it): =SUMPRODUCT(--ISNUMBER(SEARCH("this",A1:A10)),--(B1:B10="Closed")) Just modify that to the range your data is actually in. Does that help? -- Regards, Dave "Robert Brown" wrote: I need a function that adds up a column of part of a string(ex. this_that, and i just need to make sure "this" is in the string) and then another column also has to be equal to "Closed". I haven't been able to get anything to work, any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Using ADD function within DCOUNT criteria | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) |