Summation question
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. |
Summation question
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. |
Summation question
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. |
Summation question
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. |
Summation question
=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. |
Summation question
"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})) |
Summation question
"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. |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com