Analysis

[Felix212]

Dear Friends

Please see the attached Excel sheet, from the data of sheet # 2, I wanted to show sales by month in sheet no.1
Kindly assist.

[MrShorty]

Seems like a simple pivot table should be able to do this (https://www.excel-easy.com/data-****...ot-tables.html ):

1) Select the source table in sheet 2 -> Insert -> Pivot table -> choose desired pivot table options.
2) Select item code as the row labels, date as the column labels, and sum of sales as the value field.
3) Select the column labels -> Pivot table tools -> Options -> group selection -> group by month.

Will that work for you?

[Pete_UK]

You could put this formula in B9 of Sheet1:

=SUMIFS(Sheet2!$D:$D,Sheet2!$B:$B,">"&EOMONTH(B$8,-1),Sheet2!$B:$B,"<="&B$8,Sheet2!$C:$C,$A9)

then copy across and down as required.

Hope this helps.

Pete

[Felix212]

Dear Pete,

Thanks, it works. But need to validate; did that through Pivot Table and the Net total doesn't match?!
Would be able to guide me through the formula? As a matter of fact, I tried SumIFS in simple terms earlier,

Many thanks!

[Pete_UK]

My formula looks to see if the dates in column B of Sheet2 are between the last day of the previous month and the date given on row 8, and that column C is the same Product family, and if so it adds the values in column D.

Hope this helps.

Pete

[Pete_UK]

I've just noticed, though, that in N8 you are adding 366 to the date in B8, so when you get to P8 you have 1st April 2017 instead of 31st March - this throws all the other dates out. The date you have in C8 is actually 28th February 2016, rather than 29th. It would be better to use this formula in C8:

=EOMONTH(B8,1)

then copy that all the way across.

Hope this helps.

Pete

[Felix212]

Thanks Shortly!
Yes, I indeed tried that. It consolidates year 16,17,18 under each month. But, I need them to see by month by year.

[shg]

Select Months and Years in the grouping options.

[Special-K]

in Sheet1!B9
=SUMPRODUCT((Sheet2!$C$2:$C$10000=$A9)*(YEAR(Sheet2!$B$2:$B$10000)=YEAR(B$8))*(MONTH(Sheet2!$B$2:$B$10000)=MONTH(B$8))*(Sheet2!$D$2:$D$10000))
copy across and down the grid