naming a range in table

[jpbisani]

I have made a table. To know the last row in table in J1 I have entered formula SUMPRODUCT(MAX((Table2<>"")*ROW(Table2))). Then I have named a range bookdate by formula =Sheet1!$B$2:INDIRECT("b"&Sheet1!$J$1) and other range Particulars by formula Sheet1!$D$2:INDIRECT("d"&Sheet1!$J$1) and in sheet 2 I have entered a formula =SUMPRODUCT((MONTH(bookdate)=4)*(YEAR(bookdate)=2017)*(particulars=Sheet1!D4),issues). The results shows #value. I am not able to rectify mistake. And whereas I have made same in sheet 3 & 4 without table it works. Attaching worksheet to explain it fully. Eagerly awaiting for your replies to know my error.

[avk]

can you show manually in your file where & which output required.

[Richard Buttrey]

Hi,

Why not use the simple SUMIFS() function. e.g.

Please Login or Register  to view this content.

of course since there are no April 2017 dates in your table it will currently return zero. To make this more usable most people hold the start and end date required in two cells and substitute the cell refs for DATE() function

Alternatively just use a Pivot Table and Group the dates by month.

[jpbisani]

Formula where i have not taken table in sheet 4 B6 it takes from sheet 3 F29. and in sheet 2 B6where it takes report from table(sheet1 ) the answer comes as #value. I am interested in doing in table because it has more than 20000 rows.the formula which i have shown in sheet 4 B6 such around 10 formulas are there from B7 to b13. to make it faster only i am interested in table(sheet1 report sheet2)// in ranges(sheet 3 report sheet4) it is perfect. hope i have explained what is required. My main is to make it faster without VBA.

[vlady]

Isn't it tables have dynamic range ?

Please Login or Register  to view this content.

[jpbisani]

Thank you very much, appreciate & valuse your efforts