sumif

[Anonymous]

Hi

I have these there table:

item number	per unit
100	90
200	40
300	30
500	20
400	50

item number	Date	qty
100	5/20/2016	1000
200	6/20/2016	2000
300	7/20/2016	3000
400	8/20/2016	4000
100	5/20/2017	2000
200	6/20/2017	3000
300	7/20/2017	4000
400	8/20/2017	5000

Date	year	month
5/20/2016	2016	5
6/20/2016	2016	6
7/20/2016	2016	7
8/20/2016	2016	8
5/20/2017	2017	5
6/20/2017	2017	6
7/20/2017	2017	7
8/20/2017	2017	8

How would I calculate the total Qty by per unit based on year?

the result should be like these:

Row Labels	qty/per unit
2016	241
2017	331
Grand Total	572

and the detail table is:

item number	Date	qty	unit	Qty/per unit	year	month
100	5/20/2016	1000	90	11.11111111	2016	5
200	6/20/2016	2000	40	50	2016	6
300	7/20/2016	3000	30	100	2016	7
400	8/20/2016	4000	50	80	2016	8
100	5/20/2017	2000	90	22.22222222	2017	5
200	6/20/2017	3000	40	75	2017	6
300	7/20/2017	4000	30	133.3333333	2017	7
400	8/20/2017	5000	50	100	2017	8

[sunny_talwar · Accepted solution]

Try this expression

Sum(qty/[per unit])

[Anonymous]

Thank you