Trying to get data into a table

[Jamescantuseexcel]

Hi There

I’m not exactly a new use of excel I use it for vlookupsetc.

But I’m having a real hard time trying to convert some datainto a specific table.

I start with three columns of data listed as follows:

Part Number, Quantity, Date


My table has the part numbers on the horizontal axis and thedates on the vertical axis.


What I want is to get is the quantity for the specific partnumber in relation to the corresponding date.



Is there a formula in excel I can use? I’ve tried the DGET,VLOOKUP, HLOOKUP etc but they only seem to work from two datasets rather thanthree.



Anyone able to help?

 

[pwhatley]

Hi James,

Are you sure a pivot table won't be a better solution? If not a sumifs formula should work:

If your Part Number, Quantity and Date are in columns A, B and C with the header on row 1, try this formula for the first cell in your table, assuming your dates are in row 1. This formula will sum everything in column B, that has a Part Number matching the value in cell F2 and the date in cell G1.

Hope that helps.

=SUMIFS($B$2:$B$17,$A$2:$A$17,$F2,$C$2:$C$17,G$1)