|
IF (table)
4k 16bit 4k 16bit 4k 10bit 4k 16bit HD 4:4:4 N/A N/A N/A
76.48 76.48 50.9 76.48 8.29 0 0 0 6 25 25 6 0 0 0 0 1 1 1 2 0 0 0 0 458.88 1912 1272.5 917.76 0 0 0 0 Hi I have a table and I want to do the following: Parse table "CResults" row A for any "4k*" with a condition of "6" in row 3 and if true "sum" all instances in row 4. All data is in CResults. Your help appreciated |
=SUMPRODUCT(--(ISNUMBER(FIND("4k",1:1))),--(3:3=6),4:4)
-- HTH Bob Phillips "DC" wrote in message ... 4k 16bit 4k 16bit 4k 10bit 4k 16bit HD 4:4:4 N/A N/A N/A 76.48 76.48 50.9 76.48 8.29 0 0 0 6 25 25 6 0 0 0 0 1 1 1 2 0 0 0 0 458.88 1912 1272.5 917.76 0 0 0 0 Hi I have a table and I want to do the following: Parse table "CResults" row A for any "4k*" with a condition of "6" in row 3 and if true "sum" all instances in row 4. All data is in CResults. Your help appreciated |
Thanks Bob, but I seem to missing something here.
The resuilt I get for the formula (against the named array) is 0 where I expect a 3. Am I missing something here? "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("4k",1:1))),--(3:3=6),4:4) -- HTH Bob Phillips "DC" wrote in message ... 4k 16bit 4k 16bit 4k 10bit 4k 16bit HD 4:4:4 N/A N/A N/A 76.48 76.48 50.9 76.48 8.29 0 0 0 6 25 25 6 0 0 0 0 1 1 1 2 0 0 0 0 458.88 1912 1272.5 917.76 0 0 0 0 Hi I have a table and I want to do the following: Parse table "CResults" row A for any "4k*" with a condition of "6" in row 3 and if true "sum" all instances in row 4. All data is in CResults. Your help appreciated |
Is the 6 a text field, if so, use quotes.
-- HTH Bob Phillips "DC" wrote in message ... Thanks Bob, but I seem to missing something here. The resuilt I get for the formula (against the named array) is 0 where I expect a 3. Am I missing something here? "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("4k",1:1))),--(3:3=6),4:4) -- HTH Bob Phillips "DC" wrote in message ... 4k 16bit 4k 16bit 4k 10bit 4k 16bit HD 4:4:4 N/A N/A N/A 76.48 76.48 50.9 76.48 8.29 0 0 0 6 25 25 6 0 0 0 0 1 1 1 2 0 0 0 0 458.88 1912 1272.5 917.76 0 0 0 0 Hi I have a table and I want to do the following: Parse table "CResults" row A for any "4k*" with a condition of "6" in row 3 and if true "sum" all instances in row 4. All data is in CResults. Your help appreciated |
Many thanks Bob, but I still struggled to get that to work. Waht I do have is
not elegant at all. Any ideas? This seems to work: =IF(ISERROR(SEARCH("4k",Hosts!$E10)),0,Hosts!$E13) +IF(ISERROR(SEARCH("4k",Hosts!$F10)),0,Hosts!$F13) +IF(ISERROR(SEARCH("4k",Hosts!$G10)),0,Hosts!$G13) +IF(ISERROR(SEARCH("4k",Hosts!$H10)),0,Hosts!$H13) +IF(ISERROR(SEARCH("4k",Hosts!$I10)),0,Hosts!$I13) +IF(ISERROR(SEARCH("4k",Hosts!$J10)),0,Hosts!$J13) +IF(ISERROR(SEARCH("4k",Hosts!$K10)),0,Hosts!$K13) +IF(ISERROR(SEARCH("4k",Hosts!$L10)),0,Hosts!$L13) "Bob Phillips" wrote: Is the 6 a text field, if so, use quotes. -- HTH Bob Phillips "DC" wrote in message ... Thanks Bob, but I seem to missing something here. The resuilt I get for the formula (against the named array) is 0 where I expect a 3. Am I missing something here? "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("4k",1:1))),--(3:3=6),4:4) -- HTH Bob Phillips "DC" wrote in message ... 4k 16bit 4k 16bit 4k 10bit 4k 16bit HD 4:4:4 N/A N/A N/A 76.48 76.48 50.9 76.48 8.29 0 0 0 6 25 25 6 0 0 0 0 1 1 1 2 0 0 0 0 458.88 1912 1272.5 917.76 0 0 0 0 Hi I have a table and I want to do the following: Parse table "CResults" row A for any "4k*" with a condition of "6" in row 3 and if true "sum" all instances in row 4. All data is in CResults. Your help appreciated |
DC wrote: [...] This seems to work: =IF(ISERROR(SEARCH("4k",Hosts!$E10)),0,Hosts!$E13) + IF(ISERROR(SEARCH("4k",Hosts!$F10)),0,Hosts!$F13)+ IF(ISERROR(SEARCH("4k",Hosts!$G10)),0,Hosts!$G13)+ IF(ISERROR(SEARCH("4k",Hosts!$H10)),0,Hosts!$H13)+ IF(ISERROR(SEARCH("4k",Hosts!$I10)),0,Hosts!$I13)+ IF(ISERROR(SEARCH("4k",Hosts!$J10)),0,Hosts!$J13)+ IF(ISERROR(SEARCH("4k",Hosts!$K10)),0,Hosts!$K13)+ IF(ISERROR(SEARCH("4k",Hosts!$L10)),0,Hosts!$L13) =SUMIF(Hosts!$E$10:$L$10,"4k*",Hosts!$F$13:$L$13) |
IF (table)
Does this not work
=SUMPRODUCT(--(ISNUMBER(FIND("4k",10:10))),13:13) or even =SUMPRODUCT(--(ISNUMBER(FIND("4k",E10:L10))),E13:L13) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "DC" wrote in message ... Many thanks Bob, but I still struggled to get that to work. Waht I do have is not elegant at all. Any ideas? This seems to work: =IF(ISERROR(SEARCH("4k",Hosts!$E10)),0,Hosts!$E13) +IF(ISERROR(SEARCH("4k",Ho sts!$F10)),0,Hosts!$F13)+IF(ISERROR(SEARCH("4k",Ho sts!$G10)),0,Hosts!$G13)+I F(ISERROR(SEARCH("4k",Hosts!$H10)),0,Hosts!$H13)+I F(ISERROR(SEARCH("4k",Host s!$I10)),0,Hosts!$I13)+IF(ISERROR(SEARCH("4k",Host s!$J10)),0,Hosts!$J13)+IF( ISERROR(SEARCH("4k",Hosts!$K10)),0,Hosts!$K13)+IF( ISERROR(SEARCH("4k",Hosts! $L10)),0,Hosts!$L13) "Bob Phillips" wrote: Is the 6 a text field, if so, use quotes. -- HTH Bob Phillips "DC" wrote in message ... Thanks Bob, but I seem to missing something here. The resuilt I get for the formula (against the named array) is 0 where I expect a 3. Am I missing something here? "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(FIND("4k",1:1))),--(3:3=6),4:4) -- HTH Bob Phillips "DC" wrote in message ... 4k 16bit 4k 16bit 4k 10bit 4k 16bit HD 4:4:4 N/A N/A N/A 76.48 76.48 50.9 76.48 8.29 0 0 0 6 25 25 6 0 0 0 0 1 1 1 2 0 0 0 0 458.88 1912 1272.5 917.76 0 0 0 0 Hi I have a table and I want to do the following: Parse table "CResults" row A for any "4k*" with a condition of "6" in row 3 and if true "sum" all instances in row 4. All data is in CResults. Your help appreciated |
IF (table)
If you are determined to dismiss SumIf in favor of SumProduct, the
answer certainly yes. Bob Phillips wrote: Does this not work =SUMPRODUCT(--(ISNUMBER(FIND("4k",10:10))),13:13) or even =SUMPRODUCT(--(ISNUMBER(FIND("4k",E10:L10))),E13:L13) |
| All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com