Do I use MATCH to do this?
 

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)

Do I use MATCH to do this?
 

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Kevin Baker" schrieb im Newsbeitrag
om...
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)

Do I use MATCH to do this?
 

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.

--

"Kevin Baker" wrote in message
om...
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)

Do I use MATCH to do this?
 

=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.

--
Regards,
Tom Ogilvy

"Kevin Baker" wrote in message
om...
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)