Sumif with multiple criteria

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Sumif with multiple criteria

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

    Default Sumif with multiple criteria

     
    Hi all

    I've got a problem with a SUMIF function and cannot find the solution on my own - looking through the forum didn't help either, the problems are similar but still not the same!

    Following formula should return a sum in a column (which is defined by the value in A1). However I want it to return the corresponding value in all the rows with a 10, 11 or 12, not just for e.g. "10".

    The formula that "works" looks like this:
    =SUMIF('Sheet1'!$I$9:$I$44,10,INDEX('Sheet1'!$AT$9:$BX$44,0,MATCH($A$1,'Sheet1'!$AT$7:$BX$7,0)))

    What I want it to do is however this (and more elegantly than just copying the formula 3 times and just changing the criteria each time):

    =SUMIF('Sheet1'!$I$9:$I$44,{10&11&12},INDEX('Sheet1'!$AT$9:$BX$44,0,MATCH($A$1,'Sheet1'!$AT$7:$BX$7,0)))

    Any ideas?

    Many thanks!

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif with multiple criteria

    Just a quickie - have a look at SUMPRODUCT - great for multiple critera counts/sums like yours.
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif with multiple criteria

      
    Hi,

    =SUM(SUMIF('Sheet1'!$I$9:$I$44,{10,11,12},INDEX('Sheet1'!$AT$9:$BX$44,0,MATCH($A$1,'Sheet1'!$AT$7:$BX$7,0))))

    HTH

User Tag List

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
  •  

 

 
DMCA.com