V Look up function - Microsoft Community

i have one  sheet below values

if have sum total of sell rate & gp date how do it.

for eg, 04/09/2017 have 6 rows values need sum , show 1 value. how use v-look function achieve objective. there better way?

sr no	date	weight	vendor billing	sell rate	gp
1	3-sep	4.9 kgs	280	328.18	48.18
2	4-sep	0.5 kg	22.17	26.6	4.43
3	4-sep	20.5 kg	467.43	560.81	93.38
4	4-sep	19.5 kg	451.22	541.45	90.23
5	4-sep	0.5 kg	101.44	101.44	0
6	4-sep	276 kg	2440	2760	320
7	4-sep	280 kg	2475	2800	325
8	5-sep	3 kg	222.62	675.93	453.31
9	5-sep	0.5 kg	41.92	66.51	24.59
10	5-sep	6.5 kg	660.77	737.92	77.15
11	5-sep	5.5 kg	224.41	269.26	44.85
12	5-sep	0.5 kg	68.97	82.76	13.79

hi,

if have 2 sheets (named sheet1 , sheet2) in sheet2 have second table put formula in cell b2 , drag down:

=sumifs(sheet1!$e$2:$e$13,sheet1!$b$2:$b$13,sheet2!$a2)

or formula:

=sumproduct((sheet1!$b$2:$b$13=sheet2!$a2)*(sheet1!$e$2:$e$13))

those formula give sum of column e (sell rate in sheet1) each respective unique dates in sheet2.

you can download form link below file have made purpose.

https://1drv.ms/x/s!ah_p7p0xkr5rhflmpwiuv2ctautv

regards,

iliru

if reply answer question, please mark as answer.

Office / Excel / Other/unknown / Office 2007