+ Reply to Thread
Results 1 to 2 of 2

Array Formula - two worksheets, two criteria

  1. #1
    Registered User
    Join Date
    12-19-2011
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    1

    Array Formula - two worksheets, two criteria

    Hi,

    I'm trying to sum data from one worksheet in another work sheet based on two different criteria. I had no problem getting data from sheet 1 when I just had to do an array formula with one criterion, but I can't seem to get the right syntax now that I have two criteria. Any thoughts? This was my attempt:

    =SUM(IF('Sheet 1'!$E$24:$E$64='Sheet 2'!$B32 AND 'Sheet 1'!$J$24:$J$64='Sheet 2'!$A33,'Sheet 1'!$F$24:$F$64))


    So, if data in col E, sheet 1 meets the criteria in B32,sheet 2 and the data in col J, sheet 1 meets the criterion in A33, sheet 2, I want all of the values from col F, sheet 1 summed in sheet 2. Hopefully I'm making decent sense...any help appreciated!

    Thanks,
    Tara

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Array Formula - two worksheets, two criteria

    Can;t you just use sumproduct, e.g.

    =SUMPRODUCT((Sheet1!E24:E64=Sheet2!B32)*(Sheet1:J24:J64=Sheet2!A33)*(Sheet1:F24:F64))

    and do away with that nasty array formula?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1