Count Cells with text based on two criteria to be matched

  • Thread starter Thread starter hossa_dude
  • Start date Start date
H

hossa_dude

Hi there...back again :)

This time I want to do the following:
I have table with different columns, each containing text.
Now, I want to count the occurances of a specific combination o
strings.
Currently I'm using the following formula:
={SUM((Sheet1!$N$2:$N$1800="Active")*(Sheet1!$R$2:$R$1800="2048k"))}

The issue I have with this formula is, that the both strings have to b
machted exactly. What I want to do instead is count the occurances a
well if only a part of those strings is found in any of the cells (e.g
look for A* instead of Active or Aktiv etc...)

Any Idea on that? I tried doing something with "find" but didn't wor
out..(lack of my skills i suppose ;(

Kind regards
Joer
 
*Mea culpa*
::*Mea Culpa*::

Sorry guys...sometimes I'm simply too stupid to copy and past fraction
of different formulas.....
I now use:
=SUM((ISNUMBER(FIND("-T1";Sheet1!$B$2:$B$1800)))*(Sheet1!$N$2:$N$1800="Active"))
Which works neatly :)

So just forget about my question...move on...there's nothing to be see
here :
 
hossa_dude > said:
Currently I'm using the following formula:
={SUM((Sheet1!$N$2:$N$1800="Active")*(Sheet1!$R$2:$R$1800="2048k"))}

The issue I have with this formula is, that the both strings have to
be machted exactly. What I want to do instead is count the occurances
as well if only a part of those strings is found in any of the cells
(e.g. look for A* instead of Active or Aktiv etc...)
Click to expand...
....

If you want to select all cells in SomeRange containing A as the first
character, use something like

(LEFT(SomeRange,1)="A") or EXACT(LEFT(SomeRange,1),"A")

the latter is case-sensitive. If you want to match all cells containing A
anywhere, use something like

ISNUMBER(SEARCH("A",SomeRange)) or ISNUMBER(FIND("A",SomeRange))
 
Back
Top