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.