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.

 

[Aladin Akyurek]

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)))

$B2 and $A3 are both single values, so I'd think we should better try an equality test...

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

 

[flora1]

$B2 and $A3 are both single values, so I'd think we should better try an equality test...

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

thank you very much Aladin