Results 1 to 4 of 4

Thread: SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Location
    Madison, WI
    Posts
    226
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found

    I don't know if this is possible, but I have tried and failed to do the following using sumproduct and sumif(s).

    Sum all values in array 'Tabbing Data'!$A$2:$CA$5000

    Where column header = a value in a reference matrix, e.g. 'Tabbing Data'!$A$1:$CV$1='Data Gap Check'!$P47

    And where 'Data Gap Check'!$Q47 = "Yes"

    The formula would live in the 'Data Gap Check' sheet.

    Any help would be immensely appreciated.
    Aimee
    Last edited by Aimee S.; Sep 18th, 2019 at 11:33 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,218
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found

    Maybe
    =IF($Q47="Yes",SUMPRODUCT(('Tabbing Data'!$A$1:$CV$1=$P47)*('Tabbing Data'!$A$2:$CV$5000)),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Sep 2010
    Location
    Madison, WI
    Posts
    226
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found

    Alas, I get a #VALUE ! Thanks and great idea though! I opted to just use an HLOOKUP and hide a total row at line 5000 and locked it so no one can delete it. I hate the way they provide me this data - what a pain! lol

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,218
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found

    Glad you sorted it & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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