excel - Multiple HLOOKUP summation and being able accounting for "" in one of the results -

excel - Multiple HLOOKUP summation and being able accounting for "" in one of the results -

i have matrix setup using multiple hlookups in cell derive result. due of source info having blank cells, hlookup results in #value! error if of results lookup on blank cell. cannot utilize iferror function because negate add-on , result in entire formula deriving blank result when in reality addition of numbers should result (but because of occurence of 1 blank instance, formula not work). way spreadsheet setup, cannot alter source info have 0 in blank cells, though solve issue in different way. please see example:

(hlookup(l$1&"zzz.",formula!$1:$34,$t5,false)+hlookup(l$1&"yyy",formula!$1:$34,$t5,false)+hlookup(l$1&"uuu",formula!$1:$34,$t5,false)+hlookup(l$1&"ppp",formula!$1:$34,$t5,false)+hlookup(l$1&"ccc",formula!$1:$34,$t5,false)+hlookup(l$1&"ddd",formula!$1:$34,$t5,false)

any suggestions appreciated.

your formula can re-written in different , more efficient way, viz:

=sum(sumif(formula!$1:$1,l$1&{"zzz.","yyy","uuu","ppp","ccc","ddd"},index(formula!$1:$34,$t5,)))

which has benefit search values not found not cause formula error.

even improve set 6 search strings in actual worksheet somewhere, e.g. a1:a6, in case above becomes even-more succinct:

=sumproduct(sumif(formula!$1:$1,l$1&$a$1:$a$6,index(formula!$1:$34,$t5,)))

regards

excel