Get the amount from one sheet using two criteria of another

[Mikahl]

Hi guys,

I am trying to get the amount from one sheet to populate into cells in a second sheet using two criteria from the second sheet:

Rows = Account number
Columns = Dates

The first sheet is a sales log that lists the date, account number, and the amount. I want to use columns with dates to compare against rows with acct numbers from the second sheet to get the amounts. I was going to combine vlookup and hlookup but it started to get ridiculous rather quickly. I've searched around and it looks like index and match would work. But I don't know how to do that with two sheets.

I have attached the example.

Thank you very much for any help you can offer.


Sheet1

DATE	ACCT	DEBIT
7/5/2012	1	63,838.45
7/5/2012	1	51,968.36
7/5/2012	7	26,598.81
7/2/2012	5	26,340.81
7/6/2012	3	25,462.57
7/2/2012	8	25,203.78
7/9/2012	2	24,843.50
7/10/2012	2	24,711.19
7/3/2012	2	24,630.80
7/4/2012	9	24,594.51
7/4/2012	8	24,561.19
7/5/2012	7	24,534.53
7/6/2012	7	24,502.24
7/9/2012	6	24,410.81
7/7/2012	6	24,347.97
7/3/2012	6	24,027.44
7/2/2012	1	23,923.56
7/3/2012	4	23,195.19
7/9/2012	3	23,022.71
7/5/2012	8	22,867.72
7/4/2012	9	22,864.61
7/5/2012	2	22,796.41

<tbody>
</tbody>

Sheet2

ACCT	7/2/2012	7/3/2012	7/4/2012	7/5/2012	7/6/2012
1
2
3
4
5
6
7
8
9

<tbody>
</tbody>

 

[Mikahl]

I am just using the sheet1 to populate sheet2. It is a GL that I am using to populate various spreads for different factors.

 

[Mikahl]

I figured it out. I really wish I had the time and money for the 3 month course. I don't. I am very thankful for this forum. And Robert, thank you very much sir. I can now start finding the total amount of a loan payment lost by one of our younger staff members. He needs a communications course and that may be in his immediate future Thanks again.

 

[Robert Mika]

Excel 2010
	A	B	C
1	DATE	ACCT	DEBIT
2	7/5/2012	1	63,838.45
3	7/5/2012	1	51,968.36
4	7/5/2012	7	26,598.81
5	7/2/2012	5	26,340.81
6	7/6/2012	3	25,462.57
7	7/2/2012	8	25,203.78
8	7/9/2012	2	24,843.50
9	7/10/2012	2	24,711.19
10	7/3/2012	2	24,630.80
11	7/4/2012	9	24,594.51
12	7/4/2012	8	24,561.19
13	7/5/2012	7	24,534.53
14	7/6/2012	7	24,502.24
15	7/9/2012	6	24,410.81
16	7/7/2012	6	24,347.97
17	7/3/2012	6	24,027.44
18	7/2/2012	1	23,923.56
19	7/3/2012	4	23,195.19
20	7/9/2012	3	23,022.71
21	7/5/2012	8	22,867.72
22	7/4/2012	9	22,864.61
23	7/5/2012	2	22,796.41
Sheet1

Excel 2010
	A	B	C	D	E	F
1	ACCT	7/2/2012	7/3/2012	7/4/2012	7/5/2012	7/6/2012
2	1	23923.56	0	0	115806.8	0
3	2	0	24630.8	0	22796.41	0
4	3	0	0	0	0	25462.57
5	4	0	23195.19	0	0	0
6	5	26340.81	0	0	0	0
7	6	0	24027.44	0	0	0
8	7	0	0	0	51133.34	24502.24
9	8	25203.78	0	24561.19	22867.72	0
10	9	0	0	47459.12	0	0
11
Sheet2

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(Sheet1!$A$2:$A$23=B$1),--(Sheet1!$B$2:$B$23=$A2),--(Sheet1!$C$2:$C$23))

Are you data in exactky same format as mine an the formula ranges are as in mine?

If you start you ranges like this(from headings)
=SUMPRODUCT(--(Sheet1!$A$1:$A$23=B$1),--(Sheet1!$B$1:$B$23=$A2),--(Sheet1!$C$1:$C$23))

you wil get this error .

 

[Mikahl]

I was just missing the dashes. So We're golden now! Everything works! Thank you very much, sir!

 

[all4excel]

Hi Robert,

Just a small question how were you able to post excel data to the forum as I am unable to do the same..

Thanks in advance..

all4excel

 

[Robert Mika]

Hi Robert,

Just a small question how were you able to post excel data to the forum as I am unable to do the same..

Thanks in advance..

all4excel

http://www.mrexcel.com/forum/showthread.php?515787-Forum-Posting-Guidelines

or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[all4excel]

Hey Thanks for answering RObert I will look into it certainly..