SUMPRODUCT question

[Gareth]

I have been succesfully using the following to count the number of entries
on my sheet which have either a Yes1, Yes2 or NI in rng1 and one of the
codes in the brackets in rng2.

=SUMPRODUCT(((rng1="Yes1")+(rng1="Yes2")+(rng1="NI"))*(rng2={"DB","ID","LZ",
"OP","MV","FM","NP","NA","NF","TG","DD"}))

I now find that I need to count entries which have either a Yes1, Yes2 or NI
in rng1 and one of the codes in the brackets in rng2, rng3, rng4, rng5 or
rng6 (rng2 to rng6 is represented by P2:T1001).

I have tried various combinations but with no luck.

Thanks in advance.

Gareth

 

[Daniel.M]

Hello Gareth,

Maybe this:

=SUM((rng1={"Yes1","Yes2","NI"})*MMULT(NOT(ISBLANK(P2:T1001))*
ISNUMBER(SEARCH(P2:T1001,"DB/ID/LZ/OP/MV/FM/NP/NA/NF/TG/DD")),
TRANSPOSE(COLUMN(P2:T1001)*0+1)))

Regards,

Daniel M.

 

[Harlan Grove]

I have been succesfully using the following to count the number of entries
on my sheet which have either a Yes1, Yes2 or NI in rng1 and one of the
codes in the brackets in rng2.

=SUMPRODUCT(((rng1="Yes1")+(rng1="Yes2")+(rng1="NI"))*(rng2={"DB","ID","LZ",
"OP","MV","FM","NP","NA","NF","TG","DD"}))

I now find that I need to count entries which have either a Yes1, Yes2 or NI
in rng1 and one of the codes in the brackets in rng2, rng3, rng4, rng5 or
rng6 (rng2 to rng6 is represented by P2:T1001).

So rng1 through rng6 are a single column, multiple row ranges, and rng2 through
rng6 at least are in adjacent columns (P through T)? If so, it'd be expedient to
enter Yes1, Yes2 and NI in some other range, which I'll call rng1crit, and the
other codes in a different range, which I'll call rng2_6crit. Also, name the
range containing rng2 through rng6 rng2_6. Then you could use the array formula

=SUM(--(COUNTIF(rng1crit,rng1)*MMULT(COUNTIF(rng2_6crit,rng2_6),{1;1;1;1;1})>0))

If you don't want to enter these into ranges, and if you don't want to rely on
rng2 through rng6 being adjacent, you could use

=SUMPRODUCT((rng1={"Yes1","Yes2","NI"})*(MMULT(-ISNUMBER(FIND(" "&rng2_6&" ",
" DB ID LZ OP MV FM NP NA NF TG DD ")),{1;1;1;1;1})<0))

 

[Earl Kiosterud]

Gareth,

You don't say, but I suspect from your first formula that it should count
only rows containing the combinations of values sought. What's in rng3 and
rng4 etc? Same values. Each a column? Have to have one value present from
each column to be included in the count? If so, extend your formula:

=SUMPRODUCT(((rng1="Yes1")+(rng1="Yes2")+(rng1="NI"))*(rng2={"DB","ID","LZ",
"OP","MV","FM","NP","NA","NF","TG","DD"})*rng3={"val1",
"val2"...."valn"}).....)

Since your declaring rngn = to something else, it has a value of 1 when
true. When you multiply these boolean statements (they're either TRUE or
FALSE -- no MAYBE), you're creating and AND. If any isn't true it
multiplies by 0, making the result 0 (FALSE). Only takes one of those.

 

[Gareth]

Daniel

Thanks for this, maybe I didn't explain clearly enough in my first post:

Your formula works fine but adds up all occurences of the
DB/ID/LZ/OP/MV/FM/NP/NA/NF/TG/DD when there is a Yes1, Yes2 or an NI in
rng1.

I only want it to count the record once. For example:

rng1 col P col Q col R col S col T

Yes1 DB NP

Your formula counts 2, I only want 1 to be counted

I hope thois is clearer and you can help.

Gareth