sumifs - Excel Conditional summation of rows -

hi all, have problem formula sumifs , indirect.

i want sum lines in d column not have "al" in b column.

the number of lines between sum of row formula , d6 increase , vary.

i error #value! formula is:

=sumifs(d6:indirect("r[-1]c";0);b6:indirect("r[-1]c";0);"<>al") 

examples of excel worksheet layout

the line above having formula =sum(d6:indirect("r[-1]c",0)) works great

i think formula should work doesn't.

anyone can figure out wrong formula?

thanks!

/martin

in sumifs function, criteria_range needs in single column. in indirect function using absolute start column relative end column results in multiple columns. need use absolute end column. example, change...

=sumifs(d6:indirect("r[-1]c";0);b6:indirect("r[-1]c";0);"<>al") 

to...

=sumifs(d6:indirect("r[-1]c";0);b6:indirect("r[-1]c2";0);"<>al")