Amending SUMIFS to SUMPRODUCT(SUBTOTAL(9

[Jenko_D]

Hi there, I'm struggling to convert a SUMIFS formula into a formula that all totals depending on filtered rows.

My original formula was:

=SUMIFS(V$43:V$4678,$BV$43:$BV$4678,$A43,$K$43:$K$4678,"<>Programme")

I've tried a fair few combinations SUMPRODUCT(SUBTOTAL(9,OFFSET but each time the cell returns a blank. Can someone please help?

Thanks very much!

 

[AlanY]

try

=SUMPRODUCT(SUBTOTAL(9,OFFSET(V$43,ROW(V$43:V$4678)-ROW(V43),0)),--(BV$43:BV$4678=A43),--(K$43:K$4678<>"Programme"))

 

[Jenko_D]

Hi AlanY... thanks for the help. Really appreciate it.

That formula returned a circular reference error... which makes sense now I know the formula is correct and really interrogated it.

Question... can you have the cell where you place the total (i.e. the formula above) in the same array as the formula checks... but exclude that total cell with criteria? For reference, my workbook has lots of total lines (multiple rows) for different categories that I'd have to exclude from the sum.

 

[AlanY]

you're welcome

as to your follow up i guess that will ended up with a circular reference error, not quite sure how you wanted to deal with it