Exclude items with SUMIFS

flora1

New Member
Joined
Nov 27, 2017
Messages
48
=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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Nov 27, 2017
Messages
48
Re: how to excude items with SUMIFS

thanks very much barry

the column G is numbers.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Nov 27, 2017
Messages
48

ADVERTISEMENT

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
Joined
Nov 27, 2017
Messages
48
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Nov 27, 2017
Messages
48
Re: how to excude items with SUMIFS

thanks. but it still did not work.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top