Performing conditional calculations based on character strings

  • Thread starter Thread starter Arnie
  • Start date Start date
A

Arnie

I need to count the number of cells within a specified range tha
contain a specific character string, which in this case is the wor
"Resale." The cells may contain other characters as well. Fo
example, one cell contains the string "Completion Attempts Withou
Notice Resale Business Dispatch." The next cell contains the strin
"Completion Attempts Without Notice Resale Business Non-Dispatch."
"Resale" is the key word. I tried to use "COUNTIF" for this purpose
but it seems to work only when the string of interest (e.g. "Resale"
is the only string in the cell.

For each row in which the character string of interest ("Resale") i
found, I want to test another column in that row for the existence o
the character strings "YES" or "NO."

Thanks,
Arni
 
One way

=SUMPRODUCT(--(ISNUMBER(SEARCH("Resale",A1:A100))),((B1:B100="Yes")+(B1:B100
="No")))
 
=SUMPRODUCT(--ISNUMBER(SEARCH("resale",A1:A5))*(B1:B5="yes"))

or literally if you mean "existence of character strings YES or NO (rather
than b1:b5 contain "yes" or "no" only;

=SUMPRODUCT(--ISNUMBER(SEARCH("resale",A1:A5)),--ISNUMBER(SEARCH("yes",B1:B5
)))
 
Another way would be

=SUMPRODUCT((ISNUMBER(SEARCH("Resale",A1:A100)))*(B1:B100={"Yes","No"}))

--

Regards,

Peo Sjoblom


Peo Sjoblom said:
One way

=SUMPRODUCT(--(ISNUMBER(SEARCH("Resale",A1:A100))),((B1:B100="Yes")+(B1:B100
="No")))
Click to expand...
 
Dave & Peo,

Thank you both :) for a quick and useful solution to my problem. B
examining your suggestions, I was able to determine the best way t
make it work.

Arni
 
Back
Top