Exclude items with SUMIFS

[flora1]

=SUMIFS(Data!$D$2:$D$3924,Data!$A$2:$A$3924,B$2,Data!$B$2:$B$3924,$A3)

i have my criterias,

now on the column F of Data sheet. i want to exclude if any value of these three value 115 and 145 and XXII
and in column G i want to include only if the cell starts with 6 or 5 or 7


thanks.

 

[barry houdini]

Re: how to excude items with SUMIFS

Hello flora1, welcome to MrExcel

For column G you could use a wildcard, but that doesn't work with numbers - is the data in column G numeric or alphanumeric?

 

[flora1]

Re: how to excude items with SUMIFS

thanks very much barry

the column G is numbers.

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

Try something like:

=SUMPRODUCT(Data!$D$2:$D$3924,--(Data!$A$2:$A$3924=B$2),--(Data!$B$2:$B$3924=$A3),1-ISNUMBER(MATCH(Data!$F$2:$F$3924,{115,145,"XXII"},0)),--ISNUMBER(MATCH(LEFT(Data!$BG$2:$G$3924),{5,6,7}&"",0)))

 

[flora1]

Re: how to excude items with SUMIFS

thanks very much Aladin. this is good. but cannot this be done with SUMIFS. i am now familiar with this SUMPRODUCT with --. it would be too complicated for me to modify. but if it is not possible with SUMIFS then i will live with SUMPRODUCT

 

[flora1]

Re: how to excude items with SUMIFS

also the formula returned 0, all of it worked. except this part makes the whole value back to zero --ISNUMBER(MATCH(LEFT(Data!$BG$2:$G$3924),{5,6,7}&"",0)

i then thought maybe the LEFT is missing the number of char, so i changed it to --ISNUMBER(MATCH(LEFT(Data!$BG$2:$G$3924,1),{5,6,7}&"",0) still did not work.

=SUMPRODUCT(Data!$D$2:$D$3924,--(Data!$A$2:$A$3924=B$2),--(Data!$B$2:$B$3924=$A3),1-ISNUMBER(MATCH(Data!$F$2:$F$3924,{115,145,"XXII"},0)),--ISNUMBER(MATCH(LEFT(Data!$BG$2:$G$3924),{5,6,7}&"",0)))

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

thanks very much Aladin. this is good. but cannot this be done with SUMIFS. i am now familiar with this SUMPRODUCT with --. it would be too complicated for me to modify. but if it is not possible with SUMIFS then i will live with SUMPRODUCT

SUMIFS does not admit arrays like LEFT(Data!$BG$2:$G$3924). That is, we can't refer easily to the first digit of each cell in Data!$G$2:$G$3924 within a SUMIFS formula.

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

also the formula returned 0, all of it worked. except this part makes the whole value back to zero --ISNUMBER(MATCH(LEFT(Data!$BG$2:$G$3924),{5,6,7}&"",0)

i then thought maybe the LEFT is missing the number of char, so i changed it to --ISNUMBER(MATCH(LEFT(Data!$BG$2:$G$3924,1),{5,6,7}&"",0) still did not work.

There is a typo in there. It should be:

=SUMPRODUCT(Data!$D$2:$D$3924,--(Data!$A$2:$A$3924=B$2),--(Data!$B$2:$B$3924=$A3),1-ISNUMBER(MATCH(Data!$F$2:$F$3924,{115,145,"XXII"},0)),--ISNUMBER(MATCH(LEFT(Data!$G$2:$G$3924),{5,6,7}&"",0)))

​

 

[flora1]

Re: how to excude items with SUMIFS

thanks. but it still did not work.

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

thanks. but it still did not work.

Try to post 5 rows from column F and 5 rows from column G.