Sums based on mulitple conditions

[shani1975]

Hi, I have listed what I am looking for in the attached sheet! Thanks in advance!!

UPDATE, how do I upload an excel file? The file is not listed when I try to attach it.

 

[Awesome Wally]

Read this really quick: http://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705

 

[shani1975]

https://www.dropbox.com/s/s48698ndfbswfh1/example 3.xlsx?dl=0

Here is a link to dropbox, thanks so much!

 

[Khalid NGO]

Hi @shani1975
Good day and welcome to the forum

You can use SUMIFS like:
=SUM(SUMIFS(K:K,A:A,2016,B:B,"Dec",C:C,"WEEK 3",E:E,{"OCC","RCC"}))

Regards,


PS: Your attachment have been included.

 

[shani1975]

Can we use something that does not include arrays? Thanks!

 

[Khalid NGO]

Hi,

It's a regular formula not an array formula.

Here is one more alternate:
=SUMPRODUCT((A2:A99=2016)*(B2:B99="Dec")*(C2:C99="WEEK 3")*((E2:E99="OCC")+(E2:E99="RCC")),K2:K99)

Regards,

 

[shani1975]

It is returning a zero, I should be getting the number 4 since there are 4 people that are FT that are listed as REG or INTERIM at the OCC or RCC. Am I doing something wrong?

 

[Khalid NGO]

Hi,

See the attached with both formulas, and one more way with simple Pivot.

Regards,

 

[r2c2]

If the Location codes will always be RCC or OCC, you can use this alternative.
=SUMIFS(K2:K27,A2:A27,2016,B2:B27,"Dec",C2:C27,"Week 3",E2:E27,"?CC")

But again, as @Khalid NGO suggested, use either a pivot or SUMPRODUCT or SUMIFS as they will give you accurate results when you have more items in the data.