Do I use MATCH to do this?

[Kevin Baker]

Hi everyone!

I have a workbook with several sheets.. each sheet contains daily
collections for different vending machines.. it's possible to have a
collection twice for one day. Column A is the date of the collection
and Column D is the amount collected. On my last sheet I want to use
as a summary sheet.. in cell A1 I would enter the date.. below that I
would use Cell A2 and begin to list down the column all the machines I
have (example 9701, 9702, 9703, etc) in Cell B2 I want excel to take
the date from cell A1 and match it with sheet 9701 and add the
collections up( so if I had two collections for 3/2/2004 in the
amounts of $25.00 and $20.00 excel would put $45.00 into cell B2).. is
this possible?

Thanks,
Kevin R Baker
USS SEATTLE (AOE 3)

 

[Frank Kabel]

Hi
try the following in B2
=SUMPRODUCT(--(INDIRECT("'" & A1 & "'!A1:A1000)=$A$1),INDIRECT("'" & A1
& "'!D11000))
copy down

 

[Shatin]

Check out this page:

http://www.excel-vba.com/e-formula-sumproduct.htm

Following the example, you can have a report that gives you the amount by
date and vending machine.

 

[Tom Ogilvy]

=Sumif(Indirect("'" & $A$1 & "'!$A$1:$A$1000"),A2,Indirect("'" & $A$1 &
"'!$D$1:$D$1000"))

in B2, then drag fill down the column. Worked for me.