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?
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?
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?
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:J30="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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks