Using a array with sumifs

Question

The purpose of this formula is to sum the values in K if values from column B are 20x, 200, 702, 201 and also if values in column E is only IS and also values in column L read dam. I am receiving a value of 0 and I know that the value is more than 0 I think it's my array that I am not using correctly. Below is my formula I'm currently using and a screenshot of the data.

=SUMIFS($K$2:$K$5000,$B$2:$B$5000,"*20X*","200","702","201"},$E$2:$E$5000,"IS",$L$2:$L$5000,"*dam*")

wrap it in a SUM(): =SUM(SUMIFS($K$2:$K$5000,$B$2:$B$5000,"*20X*","200","702","201"},$E$2:$E$5000,"IS",$L$2:$L$5000,"*dam*")) — Scott Craner
– Scott Craner, Commented Feb 2, 2017 at 20:11
@Luis I tried =SUM(SUMIFS($K$2:$K$5000,$B$2:$B$5000,{"20X","200","702","201"},$E$2:$E$5000,"IS",$L$2:$L$5000,"dam")) without the wild-card * , and it worked for me — Shai Rado
– Shai Rado, Commented Feb 2, 2017 at 20:14
@Luis in the sample of data you provided I don't see any matches. I don't see any "IS" in Column E, or any "dam" in Column L. are you sure you have any ? — Shai Rado
– Shai Rado, Commented Feb 2, 2017 at 20:20

Gary's Student · Accepted Answer · 2017-02-02 20:53:47Z

1

There may be problems copying the formula from the Comments. This works:

=SUM(SUMIFS($K$2:$K$5000,$B$2:$B$5000,{"*20X*","200","702","201"},$E$2:$E$5000,"IS",$L$2:$L$5000,"*dam*"))

answered Feb 2, 2017 at 20:53

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using a array with sumifs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related