counting ROWS that have 2 criteria
 

I have a chart 100 rows and 10 columns. Each row has 10 non duplicate
letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3
-poiuyhvcma and so forth. I need a formula to tell me how many rows have m
and h.

counting ROWS that have 2 criteria
 

Relying on your statement each row has 10 *non-duplicate* letters and your
data is in A1:J100 (adjust range as needed), try this:

=SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2))

array entered with Cntrl+Shift+Enter (or you'll get #VALUE).



"ekim notpmah" wrote:

I have a chart 100 rows and 10 columns. Each row has 10 non duplicate
letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3
-poiuyhvcma and so forth. I need a formula to tell me how many rows have m
and h.

counting ROWS that have 2 criteria
 

hi, jmb tried formula, got "formula contains error" ???????????? any ideas
if I am doing something wrong? Mike

"JMB" wrote:

Relying on your statement each row has 10 *non-duplicate* letters and your
data is in A1:J100 (adjust range as needed), try this:

=SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2))

array entered with Cntrl+Shift+Enter (or you'll get #VALUE).



"ekim notpmah" wrote:

I have a chart 100 rows and 10 columns. Each row has 10 non duplicate
letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3
-poiuyhvcma and so forth. I need a formula to tell me how many rows have m
and h.

counting ROWS that have 2 criteria
 

And if each row may contain one or more value of m or h, I would create a new
column to the right of the range (column k) with the formula in K1:
=SIGN(COUNTIF(A1:J1,"m")*COUNTIF(A1:J1,"h")), and make the SUM(K1:K100)

counting ROWS that have 2 criteria
 

Stephane, that works but if I have to do a lot of calculations,that is a lot
of columns. Do you know if there is a sumproduct formula using frequency that
can do this. I know that if the items are in given columns I can use
=sumproduct(--(d1:di00="m"),--(ji:j100="h")) but how do you do it when the
items are anywhere in the row??? Mike
"Stephane Quenson" wrote:

And if each row may contain one or more value of m or h, I would create a new
column to the right of the range (column k) with the formula in K1:
=SIGN(COUNTIF(A1:J1,"m")*COUNTIF(A1:J1,"h")), and make the SUM(K1:K100)

counting ROWS that have 2 criteria
 

Hard to say since you didn't post the formula you actually used. I copied it
straight from the NG into a worksheet and it worked.

Here is a slightly shorter version of the same thing. It also relies on
there not being any duplicates of m or h in the same row and assumes your
data is in A1:J100:

=SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))=2))

or if there could be duplicates, try this slightly longer version:
=SUMPRODUCT(--(MMULT(--(A1:J100="m"),TRANSPOSE(--(COLUMN(A1:J1)0)))0),--(MMULT(--(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))0))

both array entered (Cntrl+Shift+Enter). Stephanes formula will work and
will only requires one additional column. There may be a way to do it with
sumproduct/frequency, but I don't know how to do it and not come up w/a
formula that is longer/more complex than using MMULT (not to say it can't be
done). I've tested all of the formulas I posted and they give me the correct
answers (under the assumptions I've stated).

If you still have issues, copy the formula you are using directly from the
worksheet into your post.


"ekim notpmah" wrote:

hi, jmb tried formula, got "formula contains error" ???????????? any ideas
if I am doing something wrong? Mike

"JMB" wrote:

Relying on your statement each row has 10 *non-duplicate* letters and your
data is in A1:J100 (adjust range as needed), try this:

=SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2))

array entered with Cntrl+Shift+Enter (or you'll get #VALUE).



"ekim notpmah" wrote:

I have a chart 100 rows and 10 columns. Each row has 10 non duplicate
letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3
-poiuyhvcma and so forth. I need a formula to tell me how many rows have m
and h.

counting ROWS that have 2 criteria
 

Then I would use a different approach, first by making a new column (only
one!) with the formula on cell K18: =A18&B18&C18&D18&E18&F18&G18&H18&I18&J18.
As you can see, it makes the concatenation of all the cells on the row. Why
CONCATENATE function does not work with a range is beyond my understanding,
but we have to make with what we have, haven't we?

Next, I will use the following array formula (validate with Shift-Ctrl-Enter
to create curly brackets { } around the formula). It is using function FIND,
which returns the position of a character in a string, or an error if not. So
the formula is, if the range to use if K18:K19,
=SUM(NOT(ISERROR(FIND("m",K18:K19,1)))*NOT(ISERROR (FIND("h",K18:K19,1))))

One remark about column K. It works based on your description, but in case
"m" and "h" are more than one character, insert a separator like "-" between
two cells to avoid a situation where the concatenation of two cells create a
value that you do not want, like "zx" & "vy" would find "xv", while "zx" &
"-" & "vy" will work properly and will never FIND "xv".

counting ROWS that have 2 criteria
 

jmb and stephane, I figured out how to use both of your formulas.JMB I like
yours better. Thanks a lot guys. I really appreciate it , Mike.In my
searching i found a great site for explaining SUMPRODUCT xldynamic.com
look under 'Multiple Condition tests -- Sumproduct. Good luck

"JMB" wrote:

Hard to say since you didn't post the formula you actually used. I copied it
straight from the NG into a worksheet and it worked.

Here is a slightly shorter version of the same thing. It also relies on
there not being any duplicates of m or h in the same row and assumes your
data is in A1:J100:

=SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))=2))

or if there could be duplicates, try this slightly longer version:
=SUMPRODUCT(--(MMULT(--(A1:J100="m"),TRANSPOSE(--(COLUMN(A1:J1)0)))0),--(MMULT(--(A1:J100="h"),TRANSPOSE(--(COLUMN(A1:J1)0)))0))

both array entered (Cntrl+Shift+Enter). Stephanes formula will work and
will only requires one additional column. There may be a way to do it with
sumproduct/frequency, but I don't know how to do it and not come up w/a
formula that is longer/more complex than using MMULT (not to say it can't be
done). I've tested all of the formulas I posted and they give me the correct
answers (under the assumptions I've stated).

If you still have issues, copy the formula you are using directly from the
worksheet into your post.


"ekim notpmah" wrote:

hi, jmb tried formula, got "formula contains error" ???????????? any ideas
if I am doing something wrong? Mike

"JMB" wrote:

Relying on your statement each row has 10 *non-duplicate* letters and your
data is in A1:J100 (adjust range as needed), try this:

=SUM(--(MMULT((A1:J100="m")+(A1:J100="h"),TRANSPOSE(COLUM N(A1:J1)/COLUMN(A1:J1)))=2))

array entered with Cntrl+Shift+Enter (or you'll get #VALUE).



"ekim notpmah" wrote:

I have a chart 100 rows and 10 columns. Each row has 10 non duplicate
letters assigned to it. row 1 - eclmhitmxp row 2 - mqwerthvcx row 3
-poiuyhvcma and so forth. I need a formula to tell me how many rows have m
and h.