Sum One To Multiple Columns

[goss]

Hi all,

Using Excel 2010.

I would like to be able to find the sum of a varying number of columns based on user defined criteria.

If user defines column 6, just colmn 6 (period end total)
If column 5 : sum(1-5)
If column 4 : sum(1-4)
And so forth...

My formula currently works for 1 column only. How may I adapt to variable columns per above?

=SUMPRODUCT((Data!$A$2:$A$2020=$B7)*(Data!$E$2:$E$2020=K$6)*(INDEX(Data!$F$2:$K$2020,0,MATCH(WK,Data!$F$1:$K$1,0))))

WK is a defined name on the setup tab where the user may enter 1-6

 

[T. Valko]

Hi all,

Using Excel 2010.

I would like to be able to find the sum of a varying number of columns based on user defined criteria.

If user defines column 6, just colmn 6 (period end total)
If column 5 : sum(1-5)
If column 4 : sum(1-4)
And so forth...

My formula currently works for 1 column only. How may I adapt to variable columns per above?

=SUMPRODUCT((Data!$A$2:$A$2020=$B7)*(Data!$E$2:$E$2020=K$6)*(INDEX(Data!$F$2:$K$2020,0,MATCH(WK,Data!$F$1:$K$1,0))))

WK is a defined name on the setup tab where the user may enter 1-6

Try this...

=SUMPRODUCT((Data!$A$2:$A$2020=$B7)*(Data!$E$2:$E$2020=K$6)*(IF(WK=6,Data!$K$2:$K$2020,Data!$F$2:INDEX(Data!$F$2:$K$2020,0,MATCH(WK,Data!$F$1:$K$1,0)))))

I don't think you need that MATCH function. Try removing it and see if the formula does what you want without it.

=SUMPRODUCT((Data!$A$2:$A$2020=$B7)*(Data!$E$2:$E$20=K$6)*(IF(WK=6,Data!$K$2:$K$2020,Data!$F$2:INDEX(Data!$F$2:$K$2020,0,WK))))

 

[goss]

Thanks Biff,

I took out the MATCH and the formula returned #N/A.
As soon as I put put it back in, it worked great

Thanks for your help

 

[T. Valko]

Thanks Biff,

I took out the MATCH and the formula returned #N/A.
As soon as I put put it back in, it worked great

Thanks for your help

Ok, good deal. Thanks for the feedback!