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.

 

[flora1]

Re: how to excude items with SUMIFS

thanks. here is uploaded file example.

https://1drv.ms/x/s!AlfEVNV8SKm1c4izTHi6G8dfloc

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

thanks. here is uploaded file example.

https://1drv.ms/x/s!AlfEVNV8SKm1c4izTHi6G8dfloc

2011 in I2 is a text number, not a true number. Try:

=SUMPRODUCT($D$2:$D$9,--($A$2:$A$9=I$2+0),1-ISNUMBER(MATCH($B$2:$B$9,{115,145,"XXII"},0)),--ISNUMBER(MATCH(LEFT($C$2:$C$9),{5,6,7}&"",0)))

 

[flora1]

Re: how to excude items with SUMIFS

2011 in I2 is a text number, not a true number. Try:

=SUMPRODUCT($D$2:$D$9,--($A$2:$A$9=I$2+0),1-ISNUMBER(MATCH($B$2:$B$9,{115,145,"XXII"},0)),--ISNUMBER(MATCH(LEFT($C$2:$C$9),{5,6,7}&"",0)))

Aladin, you are amazing!

thank you so much.

 

[flora1]

Re: how to excude items with SUMIFS

i am trying the same thing in my module. it worked in small dummy example. but it fails in my actual work template.

i have uploaded Aladin.xlsx file here.

https://1drv.ms/x/s!AlfEVNV8SKm1dRjodvBi7lkRlro

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

i am trying the same thing in my module. it worked in small dummy example. but it fails in my actual work template.

i have uploaded Aladin.xlsx file here.

https://1drv.ms/x/s!AlfEVNV8SKm1dRjodvBi7lkRlro

There is no formula in the file, also no conditions...

 

[flora1]

Re: how to excude items with SUMIFS

There is no formula in the file, also no conditions...

sorry for this.

here is the formula =SUMPRODUCT(DataAMOUNT,--ISNUMBER(MATCH(DataYear,{2011,2012},0)),1-ISNUMBER(MATCH(DataMonth,{111},0)),--ISNUMBER(MATCH(LEFT(DataPRODUCT),{5,6,7}&"",0)))

 

[flora1]

Re: how to excude items with SUMIFS

Aladin,

there is some weird problem with Excel.

https://1drv.ms/x/s!AlfEVNV8SKm1dqugHTk0Mvmk4_c

i have uploaded the sample here. if you put the formula

=SUMPRODUCT(DataAMOUNT,--ISNUMBER(MATCH(DataYear,{2011},0)),1-ISNUMBER(MATCH(DataMonth,{111},0)),--ISNUMBER(MATCH(LEFT(DataPRODUCT),{5,6,7}&"",0)))

in any sheet, it works. but if you put it in that one single sheet called "put formula anywhere here" then the formula returns zero, no matter what.

what is the problem with this worksheet? why the formula return zero here and not in other sheets?

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

Aladin,

there is some weird problem with Excel.

https://1drv.ms/x/s!AlfEVNV8SKm1dqugHTk0Mvmk4_c

i have uploaded the sample here. if you put the formula

=SUMPRODUCT(DataAMOUNT,--ISNUMBER(MATCH(DataYear,{2011},0)),1-ISNUMBER(MATCH(DataMonth,{111},0)),--ISNUMBER(MATCH(LEFT(DataPRODUCT),{5,6,7}&"",0)))

in any sheet, it works. but if you put it in that one single sheet called "put formula anywhere here" then the formula returns zero, no matter what.

what is the problem with this worksheet? why the formula return zero here and not in other sheets?

I have deleted "Main" in your file. Added a new sheet and name this one Main. Implemented the formula as is. And it works as advertised.

By the way, I have replace your definitions for DataAMOUNT, etc. which ones that will not slow down your workbook as much as older definitions did.

Data:

=Data!$1:$1048576

DataLrow:

=MATCH(9.99999999999999E+307,Data!$A:$A)

DataAMOUNT:

=INDEX(Data,2,MATCH("AMOUNT",INDEX(Data,1,0),0)):INDEX(Data,DataLrow,MATCH("AMOUNT",INDEX(Data,1,0),0))

DataMonth:

=INDEX(Data,2,MATCH("Month",INDEX(Data,1,0),0)):INDEX(Data,DataLrow,MATCH("Month",INDEX(Data,1,0),0))

DataPRODUCT:

=INDEX(Data,2,MATCH("PRODUCT",INDEX(Data,1,0),0)):INDEX(Data,DataLrow,MATCH("PRODUCT",INDEX(Data,1,0),0))

DataYear:

=INDEX(Data,2,MATCH("Year",INDEX(Data,1,0),0)):INDEX(Data,DataLrow,MATCH("Year",INDEX(Data,1,0),0))

 

[flora1]

Re: how to excude items with SUMIFS

thank you very much Aladin. very much appreciated!

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

thank you very much Aladin. very much appreciated!

Glad to help.