SUM with Multiple Criteria

[candygirl]

Hi There,
I'm new to this site and would love some help with a SUMIFS formula.
I want to add column "K" (which is m2) if column "A" = NSW and colomn "B" = land, build or demolish
So 3 seperate criteria in column C.
Any assistance would be much appreciated:)

[littleiitin]

Hi Candygirl,

Welcome to the board,

You are applying multiple criteria in single column.. so It is better to use sumproduct:

Use below formula: for Column C I have given my own criteria...Please change accordingly.




=SUMPRODUCT(--ISNUMBER(MATCH(B1:B5,{"Land";"Build";"Demolish"},0)),--(A1:A5="nsw"),--ISNUMBER(MATCH(C1:C5,{"abc";"def";"xxx"},0)),K1:K5)

[candygirl]

Hi littleiitin,
Thanks so much for your reply.
I tried your formula and received a 0 value.
I have attached an example.
I would like to add D, if A = NSW, B = Build, Land or Demolish and C = A, B or C.
Hope this makes sense.
Thanks again.

[Admin]

Hi,

Try

=SUMPRODUCT(--(A2:A289=F2),--ISNUMBER(MATCH(B2:B289,G2:G4,0)),--ISNUMBER(MATCH(C2:C289,H2:H4,0)),D2: D289)

where

F2 = NSW
G2:G4 = Build,Land,demolish
H2:H4 = A,B,C

HTH

[Haseeb A]

Or use SUMIFS like,


=SUM(SUMIFS(D:D,A:A,"nsw",B:B,{"build";"land";"demolish"},C:C,{"a","b","c"}))

Note: If first array separated by colon, then 2nd one should be be comma, If first array is separated by comma then 2nd should be colon.

Or as Admin suggested use SUMIFS like, ARRAY entered.


=SUM(SUMIFS(D:D,A:A,F2,B:B,G2:G4,C:C,TRANSPOSE(H2: H4)))

[candygirl]

Thanks Haseeb,
I went with the first code and it worked perfectly!.
Thanks very much.