Sumifs with AND OR {} ?

[ljubo_gr]

Hello the best excel, MREXCEL!
I have two columns, first is numbers, second is text.
I have long sumifs formula, it gets job done, but is it possible to shorten formula?

=SUMIFS(F3:F12;F3:F12;">1000";I3:I12;"<>S-10";I3:I12;"<>S-11";I3:I12;"<>S-9";I3:I12;"<>S-5";I3:I12;"<>S-6";I3:I12;"<>S-12")

This not working:

=SUMIFS(F3:F12;F3:F12;">1000";I3:I12;{"<>S-10"\"<>S-11"\"<>S-9"\"<>S-5"\"<>S-6"\"<>S-12"})

where is \ in english goes comma. Result:

={102986\70837\134594\134594\134594\134594}

Thank you in advance, God bless!

p.S. Also, thanks to ExcelIsFun, i cannot, i think, have eg. "<>S-10":"<>S-9": .... rows are dynamic, i0m using sorting.

 

[James006]

Hello,

The best alternative, in my opinion ... is to use Sumproduct() ... :wink:

http://www.myonlinetraininghub.com/excel-sumproduct-an-alternative-to-sumifs

HTH

 

[ljubo_gr]

=SUMPRODUCT((F3:F12>1000)*ISTEXT(MATCH(I3:I12;{"<>S-11"\"<>S-10"\"<>S-6"\"<>S-5"\"<>S-9"};0)))

Not working, also, is getting too long

 

[eke_rula]

You can use fomula:

=SUMPRODUCT((F3:F12>1000)*(I3:I12<>{"S-10","S-11","S-9","S-5","S-6","S-12"})*F3:F12)

 

[ljubo_gr]

You can use fomula:

=SUMPRODUCT((F3:F12>1000)*(I3:I12<>{"S-10","S-11","S-9","S-5","S-6","S-12"})*F3:F12)

Allready tried this formula, result is not 39229

=SUMPRODUCT({0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;3752\3752\3752\3752\3752\3752;6501\6501\6501\6501\6501\6501;0\31608\31608\31608\31608\31608;28976\28976\28976\28976\28976\28976;62757\0\62757\62757\62757\62757})

so I need to sum only 3752, 6501, 28976=39229 units.

 

[eke_rula]

Allready tried this formula, result is not 39229

sorry, this formula is not right, you try this formula:

=SUMPRODUCT(NOT(ISNUMBER(MATCH(I3:I12,{"S-10","S-11","S-9","S-5","S-6","S-12"},)))*(F3:F12>1000)*F3:F12)

Or

=SUM(IF(MMULT(--(I3:I12<>{"S-10","S-11","S-9","S-5","S-6","S-12"}),ROW($1:$6)^0)=6,(F3:F12>1000)*F3:F12))

Entered with Ctrl+Shift+Enter!!!

 

[ljubo_gr]

sorry, this formula is not right, you try this formula:

=SUMPRODUCT(NOT(ISNUMBER(MATCH(I3:I12,{"S-10","S-11","S-9","S-5","S-6","S-12"},)))*(F3:F12>1000)*F3:F12)

Thanks! Sumproduct is ok.

 

[eke_rula]

If you want formula shoter, you created range A1:A6={"S-10";"S-11";"S-9";"S-5";"S-6";"S-12"}, after you use this formula:

=SUMPRODUCT((COUNTIF(A1:A6,I3:I12)=0)*(F3:F12>1000)*F3:F12)

Hope to help you!!

 

[ljubo_gr]

Sorry MrExcel, me dumb! Why not sum the ones I must sum

=SUMPRODUCT(SUMIFS(F3:F12;F3:F12;">1000";I3:I12;{"S-7"\"S-8"\"S-13"\"S-14"}))

problem SOLVED!
https://www.youtube.com/watch?v=2rCel0eKNJc