# Exclude items with SUMIFS

#### flora1

##### New Member
=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.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### barry houdini

##### MrExcel MVP
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

##### New Member
Re: how to excude items with SUMIFS

thanks very much barry

the column G is numbers.

##### MrExcel MVP
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

##### New Member

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

##### New Member
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)))

##### MrExcel MVP

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.

Last edited:

##### MrExcel MVP
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

##### New Member
Re: how to excude items with SUMIFS

thanks. but it still did not work.

##### MrExcel MVP
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.

Replies
2
Views
108
Replies
3
Views
143
Replies
4
Views
88
Replies
3
Views
197
Replies
1
Views
157

1,130,169
Messages
5,640,544
Members
417,151
Latest member
ChickenTenderer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back