Sumif with multiple criteria

Thanks:  0
Likes:  0

# Thread: Sumif with multiple criteria

1. ## 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. ## Re: Sumif with multiple criteria

Just a quickie - have a look at SUMPRODUCT - great for multiple critera counts/sums like yours.

3. ## 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

#### Posting Permissions

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