Thread: SUMPRODUCT(--(A1:A8={"a","f","x"}))

I noticed a thread some time ago ...

If I want to count the number of occurences of a, f or x in a range of cells,
this formula was sugested :

=SUMPRODUCT(--(A1:A8={"a","f","x"}))

However, when I try this for multiple columns, it does not work ...
E.g. =SUMPRODUCT(--(A1:D8={"a","f","x"}))

It keeps only counting the first column

What am I doing wrong ?

Thanks.

The problem really has to do with how Matrix calculation works. I this case we want each Row-item (A1) to be tested against 3 column cases (it's actually an OR function).

Replace , with ;
=SUMPRODUCT(--(A1:A8={"a";"f";"x"}))
This will work.

Ola Sandstrom


Just for tutorial:
=SUMPRODUCT(--(A1:A3={"a","f","x"}))
on this
a
b
x
=2
change the x to f
=1
I could also ask, why use SUMPRODUCT and not SUM?
And what would happen if we used "array" COUNT, in these cases?

Hi Olasa,

indeed now it works, thanks for the answer !!

are there other formulas to use ?

if we expand it to e.g.

ab
g
u

Can we then also test with =SUMPRODUCT(--(A1:A3={"a","f","x"}))
if the letter a or f or x exist somewhere in the cell ?
Could this also give the answer =1 in this case, since a is part of ab ?


and if we have an array , like e.g.

ab.....j.....x
g......ux...y
u.......l.....p

can we then check if these letters appear per row, or in the whole array ?

Thanks.

Exact match in a Matrix:
=COUNTIF(A1:D8,"a")+COUNTIF(A1:D8,"f")+COUNTIF(A1:D8,"x")
=SUMPRODUCT(--(A1:D8={"a","f","x"})) will not work - as you know

Find a letter or a word in One cell:
One instance/cell: =COUNTIF(A1:D8,"*a*")+...
Several instances/cell: =SUMPRODUCT(LEN(I2:J3)-LEN(SUBSTITUTE(I2:J3,"a",""))/1)+...

Ola Sandstrom


Note:
One instance of "a" per cell= "ab"
Two instances of "a" per cell = "aba"
Use /1 for "a", /3 for "abc" (the length of the letter or word)
See also this recent post: http://www.excelforum.com/excel-worksheet-functions/339213-counting-specific-text-in-a-cell.html

Also...

if we expand it to e.g.

ab
g
u

=SUM(COUNTIF(A1:A3,{"*a*","*f*","*x*"}))

...confirmed with CONTROL+SHIFT+ENTER

and if we have an array , like e.g.

ab.....j.....x
g......ux...y
u.......l.....p

=SUM(COUNTIF(A1:C3,{"*a*","*f*","*x*"}))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Try the following...

F1:

=SUM(COUNTIF(A1:A10,{"Ola","Domenic"}))

G1:

=SUM(COUNTIF(OFFSET(B1:D1,SMALL(IF((A1:A10="Ola")+(A1:A10="Domenic"),ROW(A1:A10)),TRANSPOSE(ROW(INDI RECT("1:"&F1))))-CELL("row",A1),0),{"*a*";"*f*";"*x*"}))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by Bruno01

Hi Ola and Domenic,

works fine, thanks for the tip !

Can I ask one last difficulty in this problem ?

Suppose we have an additional column, let's say A (with data in B, C and D).
In A, we have a name, e.g. Ola or Domenic.

Can I then extend the formula to take only those lines/rows where or Ola or Domenic is in the first column ?

Thanks again !!
Bruno.

Tried your solution on this data :

ola ab j x
domenic g ux y
ola u l p


=SUM(COUNTIF(A1:A10,{"Ola","Domenic"}))
works, gives me 3, the number of times Ola or Domenic are in the first collumn

=SUM(COUNTIF(OFFSET(B1:D1,SMALL(IF((A1:A10="Ola")+(A1:A10="Domenic"),ROW(A1:A10)),TRANSPOSE(ROW(INDI RECT("1:"&F1))))-CELL("row",A1),0),{"*a*";"*f*";"*x*"}))
does not seem to work, it gives me #REF!

Could you explain me what exactly this does ?
Let's say I only want to count ) {"*a*";"*f*";"*x*"} where it says Domenic ...

Thanks !

These two formulas work in conjunction with one another. Therefore, make sure that either the first formula is entered in F1 or the F1 reference in the second formula is adjusted to refer to the cell containing the first formula.

Does this help?

Originally Posted by Bruno01

Tried your solution on this data :

ola ab j x
domenic g ux y
ola u l p


=SUM(COUNTIF(A1:A10,{"Ola","Domenic"}))
works, gives me 3, the number of times Ola or Domenic are in the first collumn

=SUM(COUNTIF(OFFSET(B1:D1,SMALL(IF((A1:A10="Ola")+(A1:A10="Domenic"),ROW(A1:A10)),TRANSPOSE(ROW(INDI RECT("1:"&F1))))-CELL("row",A1),0),{"*a*";"*f*";"*x*"}))
does not seem to work, it gives me #REF!

Could you explain me what exactly this does ?
Let's say I only want to count ) {"*a*";"*f*";"*x*"} where it says Domenic ...

Thanks !

Hi Domenic,

yessssss, it did the trick !!!
Thanks very much.

(now I 'll study an hour on the combinations you put in the formula :-)) )

Could you also take a look at my other excel question I put in the Excel Micellaneous part of this forum ?

Regards,
Bruno.

Just a small modification to the second formula to eliminate a function call...

=SUM(COUNTIF(OFFSET(B1:D1,SMALL(IF((A1:A10="Ola")+(A1:A10="Domenic"),ROW(A1:A10)),ROW(INDIRECT("1:"& F1)))-CELL("row",A1),0),{"*a*","*f*","*x*"}))

...confirmed with CONTROL+SHIFT+ENTER.

And yes, I'll take a look at it as soon as I get a chance. If I can provide you with a solution, I will.

Originally Posted by Bruno01

Hi Domenic,

yessssss, it did the trick !!!
Thanks very much.

(now I 'll study an hour on the combinations you put in the formula :-)) )

Could you also take a look at my other excel question I put in the Excel Micellaneous part of this forum ?

Regards,
Bruno.