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>

 

[wigi]

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.

Do you mean that you have trouble with the syntax for multiple sheets?
In that case, you can use the mouse to select the ranges, Excel will add the sheet names for you.

By the way, why not using a quick pivot table, and afterwards a copy/paste values if needed.

 

[Robert Mika]

Sheet2

*	A	B	C	D	E	F
1	ACCT	07/02/2012	07/03/2012	07/04/2012	#########	07/06/2012
2	1	23923.56	0	0	115806.81	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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:100px;"><col style="width:81px;"><col style="width:97px;"><col style="width:72px;"><col style="width:76px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT(--(Sheet1!$A$2:$A$23=B$1),--(Sheet1!$B$2:$B$23=$A2),--(Sheet1!$C$2:$C$23)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Excel 07/10
=SUMIFS(Sheet1!$C$2:$C$23,Sheet1!$A$2:$A$23,B$1,Sheet1!$B$2:$B$23,$A2)

 

[Mikahl]

I just have problems. My classroom for excel has consisted of looking at other formulas people have written in the office and google searches. I'm a novice at best. I understand syntax for multiple sheets but in my spread for work I am picking out 17 Accounts receivible out of 2,000 to make them ineligible for lending purposes (factoring/asset based lending) I need to find the amounts of the invoices for these accounts on particular days. I will also look for the amounts paid in the same way from another spread. What would be my best course of action?

 

[wigi]

Try Robert's approach above.

 

[Mikahl]

Thanks Robert. That looks like what I need. I really do appreciate the assistance. I'll reply with my success or failure shortly.

 

[Robert Mika]

Thanks Robert. That looks like what I need. I really do appreciate the assistance. I'll reply with my success or failure shortly.

You are welcome. This will work for one sheet.If you need assistance with multiple sheets you may need to look here


Bear in mind that you data must be in the same layout on all sheets

 

[Mikahl]

Are you saying that I can not pull the information from sheet1 to sheet2? Or if I have a sheet3 also, I would need to use the syntax from your link?

 

[Robert Mika]

Are you saying that I can not pull the information from sheet1 to sheet2? Or if I have a sheet3 also, I would need to use the syntax from your link?

You can from Sheet1 into Sheet2, BUT if you have more worksheets that you need do consolidate you need approach(or similar) from the link I posted.

 

[Mikahl]

Excel is giving me an error stating that a value in my formula is of the wrong data type. I am really showing my ignorance here. However, I have gone though the data on sheet2 to ensure that my dates are dates, my acct #'s are numbers, and my amounts are accounting.