2 spreadsheets multiple criteria into summary sheet

[Guest]

Hello all-

I have two sheets, sheet1 and sheet2. I am condensing sheet1 (filled with
approx 5000 rows to sort through)

Sheet1:
branch acct# name total
1 12345 misc $100.00
2 12345 misc $50.00
1 12367 oil $100.00

Sheet2:
branch acct# name total
1 12345 misc ???

If I was doing an if/then setup on this, it would be IF branch=1 AND
acct#=12345 AND name=misc on Sheet1 THEN put the total from sheet1 into the
total cell for sheet2. What would be the best way to tackle this? What
formula would I be placing in sheet2 total cell? Thanks In Advance - Jason

 

[Guest]

Try:

=SUMPRODUCT((Sheet1!$A$2:$A$4=Sheet2!A2)*(Sheet1!$B$2:$B$4=Sheet2!B2)*(Sheet1!$C$2:$C$4=Sheet2!C2)*(Sheet1!$D$2:$D$4))

I assume there are multiple entries for a given combination otherwise what
is diferent between the two sheets?

The above will cater for 1 or more entries.

HTH

 

[Sandy]

Jason
If I am reading you right then the following might do the job:-

Type the formula below into Sheet 2 A2 and then copy across to D2.
Then copy all down as far as you need to go (5000 rows you said)

=IF(AND(Sheet1!$A2=1,Sheet1!$B2=12345,Sheet1!$C2="misc"),Sheet1!A2,"")

Sort the list if you want after that

Sandy