# Thread: Sumif multi-conditions in same column (Excel 2000>)

1. ## Sumif multi-conditions in same column (Excel 2000>)

Hi,
Is there a shorter way to sum multiple values in 1 column. I need a single value that will be returned which will be referenced by other calc's!
In the example I may need to calc the sum of salaries (dummy data!) based on several departments. I have seen examples of using SumProduct to calc conditions in 2 or more columns etc, or using arrays. Is there a shorter easier way to do this, other than adding several sumif's???

2. ## Re: Sumif multi-conditions in same column (Excel 2000>)

You could use this array formula (confirm with Ctrl+Shift+Enter):

3. ## Re: Sumif multi-conditions in same column (Excel 2000>)

Another approach is with the DSUM function, but this requires that a criteria range be created...

Steve

4. ## Re: Sumif multi-conditions in same column (Excel 2000>)

Thanx Steve and Hans. The array function is what I was after, but thanx for reminding me of the DSUM function Steve. I must admit that I forgot about those DFunctions. This will also come in handy with additional criteria that could be used!
Cheers

5. ## Re: Sumif multi-conditions in same column (Excel 2000>)

The d-functions are probably one of the faster ways to setup with multiple criteria both ANDs and ORs. It does not require a temp column, nor use the resources that array functions use.

Steve

6. ## Re: Sumif multi-conditions in same column (Excel 2

A Sumproduct formula,

7. ## Re: Sumif multi-conditions in same column (Excel 2

When I try it, it only gives the SUM of the "Art"s. Is there some "trick" that I am missing? I even tried entering as an array (ctrl-shift-enter).

Steve

8. ## Re: Sumif multi-conditions in same column (Excel 2

No, no "tricks"

9. ## Re: Sumif multi-conditions in same column (Excel 2

<img src=/S/blush.gif border=0 alt=blush width=15 height=15>My mistake. Rudi's admins have a period which your formula did not...

Steve

10. ## Re: Sumif multi-conditions in same column (Excel 2

Actually it's mine, I didn't even notice the .

11. ## Re: Sumif multi-conditions in same column (Excel 2

I didn't see it when I tested it, I just assumed it only saw the first one...

Steve

12. ## Re: Sumif multi-conditions in same column (Excel 2

Taking this one step further, if you do have multiple criterias, you can also use

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A100,B1:B10,0))),D1100)

Where B1:B10 houses criteiras. Easier to maintain, but not any faster!

13. ## Re: Sumif multi-conditions in same column (Excel 2

Thanx Maxflia, I'll archive this one too. I'm always interested in how you can use multiple ways to get to the same answer. Thats why I posted this question, as I knew there must be a better way of setting up the function and improving my original function!
Thanx

It's faster.

15. ## Re: Sumif multi-conditions in same column (Excel 2000>)

Agree, in particular when a multiconditional summing is required. But, if the formula needs to be copied to a multitude of cells, the speed advantage decreases steadily for one needs to use the table method which boils down to an array formula entered in multiple cells. See my contrib in:

http://www.mrexcel.com/board2/viewtopic.php?t=60895

Page 1 of 2 12 Last

#### Posting Permissions

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