IF (table)

D

DC

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
 
D

DC

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?
 
D

DC

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)
 
A

Aladin Akyurek

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)
Click to expand...

=SUMIF(Hosts!$E$10:$L$10,"4k*",Hosts!$F$13:$L$13)
 
B

Bob Phillips

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)
 
A

Aladin Akyurek

If you are determined to dismiss SumIf in favor of SumProduct, the
answer certainly yes.
 
Top