Results 1 to 4 of 4

Thread: Sum of sum criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum of sum criteria

    Hi all,

    Sure this is simple for an expert.

    Below is the formula to return the value of A3 if the values in row C add to zero.

    =(IF(SUM(C3:F3)=0,A3,0))

    How do I sum the total of all of all the values from A3:A15 that meet the criteria using relative cell references for column C criteria?

    Thanks!

  2. #2
    New Member
    Join Date
    Apr 2017
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum of sum criteria

    Just to clarify, I can get the answer with:

    =SUMIFS(A3:A15,C3:C15,0,D3:D15,0,E3:E15,0,F3:F15,0)

    But is there a more elegant formula, for example if there were 50 criteria columns rather than 4?

    Thanks.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Sum of sum criteria

    You could put
    =SUM(C3:F3)
    in another column, drag down & then use
    =SUMIF(G3:G15,0,A3:A15)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jul 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sum of sum criteria

    Hi,

    without Helper-Column

    =SUMPRODUCT(A3:A15,--(MMULT(--(C3:F15=0),TRANSPOSE(COLUMN(C:F)^0))=COLUMNS(C:F)))



    Greetings



    Christian

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
  •