Excel 2007 : Formula to replace a pivot table

[benoj2005]

Subtotal without pivot.xlsx

Anyone able to enter a formula in the red cell?? I'm guessing it needs to be some sort of Subtotal Vlookup amalgamation

Need to be able to enter any relevant data in the orange cells and the red cell update accordingly.

Thanks

[Bob Phillips]

Use

=SUMPRODUCT((Data!$C$2:$N$2=$B$1)*(Data!$B$3:$B$9=$B$3)*(Data!$C$3:$N$9))

[Marcol]

No advantage, just another way

Please Login or Register  to view this content.

[benoj2005]

Use

=SUMPRODUCT((Data!$C$2:$N$2=$B$1)*(Data!$B$3:$B$9=$B$3)*(Data!$C$3:$N$9))

My data range is always changing in terms of numbers of rows

Have tried updating your formula to remove the row numbers, but it no longer works :

=SUMPRODUCT((Data!$C$2:$N$2=$B$1)*(Data!$B:$B=$B$3)*(Data!$C:$N))

Any ideas?

[npamcpp]

uh-oh. Don't use Sumproduct with whole column ranges. No better way to slow down a fast computer. Define dynamic range names instead and plug those into the formula.

Define these three range names

Grouping =Data!$B$3:INDEX(Data!$B:$B,MATCH("zzzz",Data!$B:$B,1))
DataTable =OFFSET(Grouping,0,1,,COUNTA(Data!$2:$2)-2)
Months =Data!$C$2:INDEX(Data!$2:$2,MATCH(99^99,Data!$2:$2,1))

Then change the sumproduct to

=SUMPRODUCT((Months=$B$1)*(Grouping=$B$3)*(DataTable))

PS: and get rid of the merged cells in row 1. Use "Center across selection" in the Alignment tab of the formatting dialog instead.

[Bob Phillips]

You should create a dynamic named range for the dates, the groups, and the numbers and use these in the formula.

[benoj2005]

uh-oh. Don't use Sumproduct with whole column ranges. No better way to slow down a fast computer. Define dynamic range names instead and plug those into the formula.

Define these three range names

Grouping =Data!$B$3:INDEX(Data!$B:$B,MATCH("zzzz",Data!$B:$B,1))
DataTable =OFFSET(Grouping,0,1,,COUNTA(Data!$2:$2)-2)
Months =Data!$C$2:INDEX(Data!$2:$2,MATCH(99^99,Data!$2:$2,1))

Then change the sumproduct to

=SUMPRODUCT((Months=$B$1)*(Grouping=$B$3)*(DataTable))

PS: and get rid of the merged cells in row 1. Use "Center across selection" in the Alignment tab of the formatting dialog instead.

Thanks, that worked a treat, have attached solution for anyone who wants it in the future. Subtotal without pivot.xlsx

Just now need to understand how these named range formulas work, is there any good articles to read??

[npamcpp]

got errors editing my post

[npamcpp]

A selection:

Charley Kyd: http://www.exceluser.com/explore/dynname1.htm
Excel is Fun video: http://www.youtube.com/watch?v=1x_hcmVqnyY
Charles Williams: http://www.decisionmodels.com/optspeedf.htm
Jan Karel Pieterse: http://www.jkp-ads.com/articles/ExcelNames07.asp

I personally use Offset only sparingly, but for range names it's often less work to use Offset rather than to construct a range name from scratch with Index. There are trade-offs regarding speed and volatility, but if your workbook is not overly large, you probably won't notice a difference.

[benoj2005]

Thanks

The links all seem to use the offset function, which I was familiar with. It's the index and match within an offset that I'm trying to get my head around. Like why you match "zzzz" or 99^99

I've implemented your names and formulas into my actual sheet (Not the simple example I posted) and its not covering the whole range due to blank rows I think. Starting to drive me mad lol

[JosephP]

=SUMIFS(INDEX(Data!$B:$N,,MATCH($B$1,Data!$B$2:$N$2,0)),Data!$B:$B,$B$3)
since you're using 2007

[benoj2005]

=SUMIFS(INDEX(Data!$B:$N,,MATCH($B$1,Data!$B$2:$N$2,0)),Data!$B:$B,$B$3)
since you're using 2007

Oh that's better, it looks messier in the formula bar than having named ranges and may take more resources, but I can't get the named ranges to cover the correct areas, so this will have to do. Thanks

[Marcol]

See this workbook for another approach to Dynamic Names, it might help.
I have used

Please Login or Register  to view this content.

To get your result, I only have 2003 at the moment.

This approach should handle blanks.

[benoj2005]

See this workbook for another approach to Dynamic Names, it might help.
I have used

Please Login or Register  to view this content.

To get your result, I only have 2003 at the moment.

This approach should handle blanks.

Thanks

I've gone with the sumif function posted by "JosephP" as this is the easiest for me to re-create.

If anyone could explain the dynamic name formulas they have posted, for Example...

Please Login or Register  to view this content.

Like that broken down, then I could understand it and use in the future.

[Marcol]

Note I have edited post #13

I'll explain later how the Dynamic Ranges work, if no one else does. Got to go for now.