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

Topic

New Reply

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???
TIA for any advice!

Reply | Quote

Replies

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

=SUM(IF((D2:D22=”Art”)+(D2:D22=”Admin.”),F2:F22))

Reply | Quote

A Sumproduct formula,

=SUMPRODUCT((D2:D22={“Art”,”Admin”})*(F2:F22))

Reply | Quote

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

Reply | Quote

No, no “tricks”

Reply | Quote

My mistake. Rudi’s admins have a period which your formula did not…

Steve

Reply | Quote

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

Reply | Quote

I didn’t see it when I tested it, I just assumed it only saw the first one…

Steve

Reply | Quote

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

=SUMPRODUCT(–(ISNUMBER(MATCH(A1:A100,B1:B10,0))),D1:D100)

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

Reply | Quote

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

Reply | Quote

It’s faster.

Reply | Quote

BTW, if you want to use an array constant to represent the criteria set…

=SUMPRODUCT((D2:D22={“Art”,”Admin”})*(F2:F22))

can be re-written as a SumIf formula:

=SUM(SUMIF(D2:D22,{“Art”,”Admin”},F2:F22))

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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%5B/url%5D

Reply | Quote