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.

 

[Jonmo1]

Re: how to excude items with SUMIFS

I saw your other thread that just got closed.
The values in B2 and A3 on Main are TEXT numbers.
Format those cells as General or Number (anything but TEXT)
AND, Re-Enter those numbers.

 

[flora1]

Re: how to excude items with SUMIFS

thanks very much Jomno1

is there a way to modify the formula, so that it works without formatting the cell ? i tried with A3&"#" but it did not work.

 

[Jonmo1]

Re: how to excude items with SUMIFS

is there a way to modify the formula, so that it works without formatting the cell ?

Why?
Are those cells in your 'actual' sheet populated by a formula that returns as text? Adjust that formula instead..

But yes, you should be able to use A3+0 and/or B2+0 in the formula.

 

[Aladin Akyurek]

Re: how to excude items with SUMIFS

thanks very much Jomno1

is there a way to modify the formula, so that it works without formatting the cell ? i tried with A3&"#" but it did not work.

I did give you the formula which works wit the data as you have it. It's sometimes better to work with data as is.

 

[flora1]

Re: how to excude items with SUMIFS

Jomno1

thanks. i did use A3+0 but it not working

=SUMPRODUCT(DataAMOUNT,--ISNUMBER(MATCH(DataYear,B$2+0)),1-ISNUMBER(MATCH(DataMonth,$A3+0,0)),--ISNUMBER(MATCH(LEFT(DataPRODUCT),{5,6,7}&"",0)))

also uploaded the file here. https://www.dropbox.com/s/42cp70kmsj0bdk7/Attached.xlsx?dl=0

 

[flora1]

Thanks Aladin.

somehow the formulas are scared of you when i come to this forum, it works, but now when i changed it by myself. i did not work. i also checked the lotus compatibilty problem, it is already unchecked.

 

[Jonmo1]

Thanks Aladin.
somehow the formulas are scared of you when i come to this forum, it works, but now when i changed it by myself. i did not work.

That implies that the formulas are scared of you, not Aladin

The +0 didn't work in the formula because the Match function is expecting a Range or Array as the 2nd argument. The +0 converts that to just a number instead.
Try DataYear&"" instead (same for DataMonth)

Also, I agree with Aladin. When your example setup changes from one post to the next, that makes it very difficult for us to work with.
You should keep your sample setup consistent, as close to the real sheet setup as possible.

 

[Aladin Akyurek]

Thanks Aladin.

somehow the formulas are scared of you when i come to this forum, it works, but now when i changed it by myself. i did not work. i also checked the lotus compatibilty problem, it is already unchecked.

Apparently, you did not implement post #18 . Read it again and here is the workbook that should support the re-read: https://www.dropbox.com/s/t0o5d9adxv13360/Aladin flora1.xlsx?dl=0

 

[flora1]

Thanks Aladin.

I implemented the post 18 and it made my formulas 100 times faster. thank you very much for that. link below is the workbook with the named ranges as per post 18

https://www.dropbox.com/s/xqv3htygfjq1ryc/Aladin.xlsx?dl=0

 

[flora1]

Jomno1

you are genius, just like Aladin. "brilliant minds think alike"

i put the double qoutes and it worked.

=SUMPRODUCT(DataAMOUNT,--ISNUMBER(MATCH(DataYear&"",B$2,0)),--ISNUMBER(MATCH(DataMonth&"",$A3,0)),--ISNUMBER(MATCH(LEFT(DataPRODUCT),{5,6,7}&"",0)))