Table without using Pivot

[malibusurf]

Hi all,

I am trying to create a template/table that shows volume by month (across the x axis), by ROPM (on the y axis), that can be filtered by BU (i.e. I'd like to be able to pick a BU from the dropdown and the data should automatically adjust on the table). Also, I am trying to accomplish this WITHOUT pivot tables, only formulas and drop down.

Thanks,

Michael

[:) Sixthsense :)]

Please show the expected output also for better understanding

[malibusurf]

Here is the output I am looking for. I created this one in a Pivot table, however. I am looking for a way to create this table with formulas and drop-down. See attached.
Thanks

[malibusurf]

I've attached the output I am looking for in the thread below, only I did it with a Pivot.
Thanks

[Marcol]

See if this table gives you a start.

Note
With 2007 and above you can use SUMIFS() rather than SUMPRODUCT()

[malibusurf]

Thanks Marcol! Great table. I'll be working on getting the BU drop down/data validation incorporated

[malibusurf]

Marcol, comparing your table results with the Pivot table I attached above, some numbers differ, especially in the later months. Do you have an idea?

[benishiryo]

hi malibusurf, fix some cells in Marcol's file & it'll be fine. for B2, use:
=IF($A2="","",IF(B$1="","",SUMPRODUCT((Sheet1!$C$2:$C$12000=$A2)*(Sheet1!$G$2:$G$12000=B$1)*Sheet1!$H$2:$H$12000)))

copy down & across. your BU dropdown might be slightly complicated if you're thinking of putting multiple values like the pivot can.

Edit: i think Excel 2011 has SUMIFS? if so, try:
=IF($A2="","",IF(B$1="","",SUMIFS(Sheet1!$H$2:$H$12000,Sheet1!$C$2:$C$12000,$A2,Sheet1!$G$2:$G$12000,B$1)))

and if i were to put a dropdown in Z1, then you could try this:

Please Login or Register  to view this content.

[Marcol]

Thanks for the catch Ben, that was just carelessness on my behalf ...

SUMIFS() can be used from 2007 onwards, again, thanks for the formula. I only have 2003 just now, hence SUMPRODUCT().

@ malibusurf
With over 10,000 rows at present, I would be tempted to introduce Dynamic Named Ranges, to ensure calculations are kept to an absolute minimum as your data grows.

[malibusurf]

Hey Benishiryo and Marcol,
Thanks for your help. The formula works great and I got my table together. However, I am still struggling with the dynamic data range data validation and avoiding duplicate BUs in the drop down selection. Do you have any more suggestions?

[malibusurf]

I got the drop down working with:

=IF(ROWS(A$6:A6)>$D$32,"",INDEX(BU,SMALL(IF(FREQUENCY(IF(BU<>"",MATCH(BU,BU&"",0)),ROW(BU)-ROW(Data!$B$2)+1),ROW(BU)-ROW(Data!$B$2)+1),ROWS(A$6:A6)))),

where BU =Data!$B$2:INDEX(Data!$B$2:$B$12000,COUNTA(Data!$B$2:$B$12000))

BUT, I am working on connecting the various data results to each BU.

Thanks for your help guys