Help choosing most efficient formula

See attachment. I have 2 sheets
Sheet 1 contains the data
Sheet 2 is where i need to have the results

I tried a sumif but it didn't work and I'm not sure if a pivot table should be used. What I want to do is sum the total of MCAU from Sheet 1 into cell B2 in Sheet 2 based on the date. So basically the total MCAU for 10/22/12, the next line will be the total for 10/23/12.

Hi rcapone

How about the INDEX & MATCH, in B2 and copy over and down.

Formula:

Please Login or Register  to view this content.

I think its interesting that SumIf didn't work. I used this formula in sheet 2 cell B2 =SUMIF(Sheet1!A2:B169,A2,Sheet1!B2:B169) and filled down. Then I used this formula for sheet 2 cell C2 = =SUMIF(Sheet1!A2:C169,A2,Sheet1!C2:C169)and filled down. If you want to use SumIf try those formulas...

I've tried but that only returns the value of Sheet1 B2.
What i'm looking for is that in Sheet 2 B2 should return a value of 1919 which is the sum of all of the MCAU values for the respective date which is 10/22/12.
Sheet 2 B3 should return 1922

I'm trying to total/sum the figure for each date

Originally Posted by shawnsonline

I think its interesting that SumIf didn't work. I used this formula in sheet 2 cell B2 =SUMIF(Sheet1!A2:B169,A2,Sheet1!B2:B169) and filled down. Then I used this formula for sheet 2 cell C2 = =SUMIF(Sheet1!A2:C169,A2,Sheet1!C2:C169)and filled down. If you want to use SumIf try those formulas...

Ah!!
I see my mistake. This is what I was using
=SUMIF(Sheet1!B1:B168,Sheet1!A1:A168=Sheet2!A2)

Thanks, your worked.

Sorry rcapone, I didn't notice that you wanted the SUM.