Results 1 to 3 of 3

Sumif with multiple criteria

This is a discussion on Sumif with multiple criteria within the Excel Questions forums, part of the Question Forums category; Hi all I've got a problem with a SUMIF function and cannot find the solution on my own - looking ...

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    4

    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

    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

    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

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