Pass Variable in Formula

[rizmomin]

Hello:

I am using the below formula and i want to see if it is possible to pass variable in the formula to give the result.

Please Login or Register  to view this content.

N$2:$N$2000 needs to be variable.
The value of variable can come from cell K1
Example:
If K1 = N then the above formula will stay as is.
If K1 = M then above formula needs to change to :

Please Login or Register  to view this content.

I hope this is clear explanation.

Please help and let me know if any questions.

Thanks
Riz

[Tony Valko]

Are those the only 2 variables?

[Lemice]

Hello,

Try this
=IF(K1="N",SUMIFS(DailySales!$N$2:$N$2000,DailySales!$B$2:$B$2000,">="&$J7,DailySales!$B$2:$B$2000,"<="&$K7,DailySales!$A$2:$A$2000,L$1),IF(K1="M",SUMIFS(DailySales!$M$2:$M$2000,DailySales!$B$2:$B$2000,">="&$J7,DailySales!$B$2:$B$2000,"<="&$K7,DailySales!$A$2:$A$2000,L$1),"K1 is not N or M"))

Hope this help.

[rizmomin]

Hi Lenice and Tony:

The value in cell K1 could be any Alphabet A-Z

Hope this explains my requirement.

Thanks

Riz

[Lemice]

I see,

Try this formula then

Please Login or Register  to view this content.

[Tony Valko]

You're already using columns A and B for criteria.

Something like this:

=SUMIFS(INDEX(DailySales!$A$2:$Z$2000,,CODE(UPPER(K1))-64),DailySales!$B$2:$B$2000,">="&$J7,DailySales!$B$2:$B$2000,"<="&$K7,DailySales!$A$2:$A$2000,L$1)

Assumes cell K1 will only contain letters A to Z.

[rizmomin]

Hi Tony:

This does not give correct result.
Let me explain you.
I have Daily Sales Data.
Column N has Sales from Category1
Column O has Sales from Category2
Column P has Sales from Category3
Column Q has Sales from Category4

and so on.

Please refer to attached excel sheet as an example.
I have 5 stores and i compare data side by side
Please let me know if any questions.

Thanks
Riz

[Tony Valko]

Try this:

=SUMIFS(INDIRECT("DailySales!"&$K$1&":"&$K$1),DailySales!$B:$B,">="&$J7,DailySales!$B:$B,"<="&$K7,DailySales!$A:$A,L$1)

[rizmomin]

Hi Tony:

Looks like this works.
Thanks a lot


Riz

[Tony Valko]

You're welcome. Thanks for the feedback!