Formula that takes information from date and looks at criteria and sums it.

[shaf1234]

Hi, hope someone can help, I had a search but couldn't find anything.

I have attached a little sample of data.

I am trying to do a formula in cell B2, Sheet 2, which looks at the table on sheet 1 and by date it will sum up the quantities relevant to the product code.
ie B2, Sheet 2, should equal 4304.

Thanks in Advance

[Fotis1991]

Try in B2. Copy down and across.

=SUMPRODUCT((Sheet1!$D$2:$D$9=Sheet2!$A2)*(Sheet1!$A$2:$A$9=B$1)*(Sheet1!$F$2:$F$9))

[shaf1234]

the results were 0's.

[Fotis1991]

The result in B2 is 4304.

[shaf1234]

Thanks for that, but I'm pretty sure you know this, I am using a table created in excel, so how do I lock the columns? when i drag the formula, it moves to the next column.

[Fotis1991]

Not sure what you mean. As you drag the formula in C2 it's get the result for 21/03/2013 and so on....

Generally speaking when you want to lock a formula you use absolutte reference for the ranges and the cells using dollar sign...

=sUMPRODUCT((Sheet1!$D$2:$D$9=Sheet2!$A$2 ............

[shaf1234]

when you create table it doesn't work like that. you cannot f4 it. make it absolute.

[shaf1234]

eg, the data on Sheet 1 convert to table, using insert table. Then if you redo your formula using the table, the drag option, moves columns, but copy and paste the formula works fine.

[shaf1234]

Mind you. your formula has solved my issue!! Thanks and there's a star to appreciate your hard work.

[Fotis1991]

......................