I am developing a manament reporting tool. The report covers 52 weeks, and there are 5 changing periods:
1. Reporting is done for each week ending on the Friday
2. The week's reporting ends on the 25th if the 25th falls on onany weekday other than a Friday
3. If the 25th falls on a Saturday...
I discovered the reason why the Pivot Table does not group dates - it does not accept any blanks in the Date column of the database. Anybody know of a work-around?
Hi,
I am experiencing two problems with my pivot table:
a) Want to group dates in months. Pivot table gives error message "Cannot group that selection". Cells in data table are formatted as dates, not text.
b) The pivot table does not reflect all the entries from my data table, e.g not all...
Thanks Hui, Chill.
Chill, I tried your sum(if ....) formula - no difference at all in the calculation speed.
Pivot table doesn't work: Does not group dates in months, says "cannot group data", and it leaves out entries.
Seems like I am stuck with having to wait.
I created a report sheet, using the following SUMPRODUCT formula: =SUMPRODUCT((Plant=$B8)*(InvoiceDate>=U$4+1)*(InvoiceDate<=V$4)*InvoiceAmount).
The report range consists of 81 columns by 250 rows, and the above formula is copied in each cell.
It takes Excel more than a minute...