Hi all,
I have a large spreadsheet and I want to make it work faster. Initially, I
perform a check in some cells of the same row (eg I1 to L1) if they contain a
specific word. Additionally, I need count that word only once in every row.
Thus the function is like:
=IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1,"*Wo rd*")). My problem is that
I need to add all the results of the functions (one from every row) and sum
them at the end, without using 80,000 separate cells. What I want to do could
is something like the next expression which is not permitted:
=SUM(IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1, "*Word*")):
IF(COUNTIF(I10000:L10000,"*Word*")1,1,COUNTIF(I10 000:L10000,"*Word*"))). Can
you please give me a good piece of advice? Thank you in advance.

Here is one way

=SUM(IF((ISNUMBER(FIND("Word",I1:I10))+ISNUMBER(FI ND("Word",J1:J10))+ISNUMBER(FIND("Word",K1:K10))+I SNUMBER(FIND("Word",L1:L10))),ROW(I1:I10)^0))

as an array formula.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"T.Mad" wrote in message
...
Hi all,
I have a large spreadsheet and I want to make it work faster. Initially, I
perform a check in some cells of the same row (eg I1 to L1) if they
contain a
specific word. Additionally, I need count that word only once in every
row.
Thus the function is like:
=IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1,"*Wo rd*")). My problem is
that
I need to add all the results of the functions (one from every row) and
sum
them at the end, without using 80,000 separate cells. What I want to do
could
is something like the next expression which is not permitted:
=SUM(IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1, "*Word*")):
IF(COUNTIF(I10000:L10000,"*Word*")1,1,COUNTIF(I10 000:L10000,"*Word*"))).
Can
you please give me a good piece of advice? Thank you in advance.

First of all, you could simplify your formula like so:

=IF(COUNTIF(A1:L1,"*word*")0,1,0)

then copy this down and then just sum the column.

Alternatively, you could put this array* formula in the cell where you
want the total to appear:

=SUM(IF(COUNTIF(INDIRECT("A"&ROW(A1:A10000)&":L"&R OW(A1:A10000)),"*word*")0,1,0))

* Note that as this is an array formula, once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it
instead of the usual ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - you must not type these yourself.

This is all one formula, so be wary of spurious line-breaks inserted
on the newsgroups. I'm not sure why you referred to 80,000 separate
cells (unless you are using Excel 2007), but I have assumed you have
data in rows up to 10,000, as in your last example - adjust as
necessary.

Hope this helps.

Pete


On Jul 3, 12:54 pm, T.Mad wrote:
Hi all,
I have a large spreadsheet and I want to make it work faster. Initially, I
perform a check in some cells of the same row (eg I1 to L1) if they contain a
specific word. Additionally, I need count that word only once in every row.
Thus the function is like:
=IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1,"*Wo rd*")). My problem is that
I need to add all the results of the functions (one from every row) and sum
them at the end, without using 80,000 separate cells. What I want to do could
is something like the next expression which is not permitted:
=SUM(IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1, "*Word*")):
IF(COUNTIF(I10000:L10000,"*Word*")1,1,COUNTIF(I10 000:L10000,"*Word*"))). Can
you please give me a good piece of advice? Thank you in advance.

One way:

=SUMPRODUCT(--((ISNUMBER(SEARCH("Word",I1:I1000)) +
ISNUMBER(SEARCH("Word",J1:J1000)) + ISNUMBER(SEARCH("Word",K1:K1000)) +
ISNUMBER(SEARCH("Word",L1:L1000)))0))

If "Word" is case-sensitive, use FIND() instead of SEARCH()


In article ,
T.Mad wrote:

Hi all,
I have a large spreadsheet and I want to make it work faster. Initially, I
perform a check in some cells of the same row (eg I1 to L1) if they contain a
specific word. Additionally, I need count that word only once in every row.
Thus the function is like:
=IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1,"*Wo rd*")). My problem is that
I need to add all the results of the functions (one from every row) and sum
them at the end, without using 80,000 separate cells. What I want to do could
is something like the next expression which is not permitted:
=SUM(IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1, "*Word*")):
IF(COUNTIF(I10000:L10000,"*Word*")1,1,COUNTIF(I10 000:L10000,"*Word*"))). Can
you please give me a good piece of advice? Thank you in advance.

=SUMPRODUCT(--ISNUMBER(SEARCH("word",I1:L10)))


"T.Mad" wrote:

Hi all,
I have a large spreadsheet and I want to make it work faster. Initially, I
perform a check in some cells of the same row (eg I1 to L1) if they contain a
specific word. Additionally, I need count that word only once in every row.
Thus the function is like:
=IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1,"*Wo rd*")). My problem is that
I need to add all the results of the functions (one from every row) and sum
them at the end, without using 80,000 separate cells. What I want to do could
is something like the next expression which is not permitted:
=SUM(IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1, "*Word*")):
IF(COUNTIF(I10000:L10000,"*Word*")1,1,COUNTIF(I10 000:L10000,"*Word*"))). Can
you please give me a good piece of advice? Thank you in advance.

"T.Mad" wrote...
....
Thus the function is like:
=IF(COUNTIF(I1:L1,"*Word*")1,1,COUNTIF(I1:L1,"*W ord*")). My problem is
that
I need to add all the results of the functions (one from every row) and sum
them at the end, without using 80,000 separate cells. . . .
....

Yet another alternative,

=COUNT(1/MMULT(--ISNUMBER(SEARCH("word",I1:L10000)),{1;1;1;1}))

"Teethless mama" wrote...
=SUMPRODUCT(--ISNUMBER(SEARCH("word",I1:L10)))
....

This returns the same result as =COUNTIF(I1:L10,"*word*"), which is not what
the OP requested.