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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
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:
Upvote 0
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)))

 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top