Using multiple SUMIF inside multiple IF stmts, looking for simpler solution.

Ok, I'm sure my title wasn't descriptive enough. I'm trying to come up with a formula in Sheet1 that will populate the sales based on the information in Sheet2. IE: I will need the sales column on Sheet1 to go 6, 7, 8, 10, 11, 12.

The formula I'm currently using is IF(Store1,SUMIFS(Month/Sales),IF(Store2,SUMIFS(Month/Sales),"0")

Now this formula works fine, but the problem is that my 6 'Stores' and over 7,000 lines of 'sales' that need to be populated. Which means the formula I have above is basically 5 or 6 IF stmts nested inside eachother 7,000 times over. Not only is it ugly and hard to follow for someone reviewing, but it's making the size of my excel file dangerously close to being too large to be loaded into a program I need to use.

Is there an easier way to achieve this without radically altering the layout of the spreadsheet?

Sheet 1

Store Month Sales
Store1 Jan
Store1 Feb
Store1 Mar
Store2 Jan
Store2 Feb
Store2 Mar

Sheet 2

Month Type Store1 Store2
Jan Sales 6 10
Jan Expenses 2 1
Feb Sales 7 11
Feb Expenses 3 2
Mar Sales 8 12
Mar Expenses 4 5


A standard SUMIFS formula would be fine, if only there was a way to tell it to sum a different column depending on which 'store' it was without using 6 IF stmts.

Have you tried a Pivot Table?

How about..

=SUMIF(Month_range,month,INDEX(C:I,,MATCH(Store,$C$1:$I$1,0)))

where Column C-I have your store values

Originally Posted by Ace_XL

How about..

=SUMIF(Month_range,month,INDEX(C:I,,MATCH(Store,$C$1:$I$1,0)))

where Column C-I have your store values

Couldn't quite get what you were saying to work, but it did give me the idea to turn my formula 'inside out'

So now instead of an IF stmt with 6 other IF stmts and related SUMIFS inside... I have 1 SUMIFS with 6 IF stmts nested in the SUM_RANGE. It effectively allowed me to remove 5 SUMIFS from my lengthy formula and reduce the character count by half.

I applied the change to two columns of similar formulas I had and reduced my file size by 20%!!

Thanks!

EDIT: What you helped me realize was that I could use an IF stmt in the SUM_RANGE of my SUMIFS formula. Didn't know it was possible. Will come in handy in the future.

Couldn't quite get what you were saying to work, but it did give me the idea to turn my formula 'inside out'

This is what I was suggesting. See highlighted columns. This should probably reduce the file size even more!

The INDEX formula simply selects the entire column between ranges (column) C:H based on the match returned by the store number. Hence Col C for 1, Col D for 2, Col E for 3 and so on..

Does this help?

Yes the ROW variable is indeed balnk and you could adapt the same to leave a column variable blank too