Excel sumif is not <>

Akbarov

Active Member
Joined
Jun 30, 2018
Messages
347
Office Version
  1. 365
Platform
  1. Windows
Hey community,

i wanna know how can i ask excel to sum if list1 is not on list2



Sheets("DATA").Cells(5, "V") = [sum(if((isnumber(match(list1,list3,0)))*(sifaris>0)*(list1<>list2)*,printed))]
Can anybody help me please?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
434300ALBALI PET 1 LT1180103ZAF41552095276256521MARKET BAKIXANOV RAZIN 1300ZAUR YRAMOVQKKHAN SADIQOV202
101900OLA PET 1 LT1180103ZAF415534991525560ZABRAT BAYISI320HABIL FTEROVQQXTIYAR RUSTAMOV2800280
434300APPY ALBALI PET 1 LT1180103ZAF4155330957625690SAR M-T RAZIN300ZAUR BAAMOVQKOHAN SADIQOV303
434300APPY ALBALI PET 1 LT1180103ZAF4155214567625620SAR MARKET AXUNDOV300ZAUR BAYMOVQSSAIR ISMAYILOV505
434300APPY ALBALI PET 1 LT1180103ZAF415528971856910DAUD MARKET BILECERI300ZAUR BAAMOVQSSMIR ISMAYILOV101
929700FANTA DUSES PET 0.5LX121180103ZAF415526401778552ESCO-1 JALE PLAZA300ZAUR BAYMOVQSSIR ISMAYILOV202
434300CAPPY ALBALI PET 1 LT1180103ZAF415526571778552ESCO-1 JALE PLAZA300ZAUR BAAMOVQSAMIR ISMAYILOV101
641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF415529211774070HIPPER MARKET300ZAUR BAAMOVQSMIR ISMAYILOV101
671200USE TEA QARPIZ PET 0,5LT1180103ZAF415529211774070HIPPER MARKET300ZAUR BAAMOVQSSAR ISMAYILOV101
434300APPY ALBALI PET 1 LT1180103ZAF41552202476257221 MARKET GENCE PROSPEKTI300UR BAYRAMOVQKOHAN SADIQOV202
671200USE TEA QARPIZ PET 0,5LT1180103ZAF415528141797770BEEQROS MARKET300ZAUR BRAMOVQSSAM ISMAYILOV303
434300APPY ALBALI PET 1 LT1180103ZAF4155229318189350AKRO MARKET MONTIN300ZAUR BRAMOQKORKHAN SAQOV808
641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF4155262447625700RAZ MARKET 6 MKR LIBRA300UR BAYRAMOVQSSAMIR ISMAYOV202
671200SE TEA QARPIZ PET 0,5LT1180103ZAF4155317618149170EGA MARKET INQLAB300ZAUR BAYMOVQSSAMIR ISMILOV202
641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF4155268117669921RAZ MARKET OLIMPIKSTAR300ZAUR BAAMOVQSSAMIR ISMAYOV303
929700NTA DUSES PET 0.5LX121180103ZAF4155268117669921RAZ MARKET OLIMPIKSTAR300ZAUR BAYROVQSSAMIR ISMAYOV101
434300APPY ALBALI PET 1 LT1180103ZAF41552103176835418STORE MARKET AYGUN CITY300ZAUR BAAMOVQKORKHAN SADOV505
929700NTA DUSES PET 0.5LX121180103ZAF41552111176835418STORE MARKET AYGUN CITY300ZR BAYROVQKORKHAN SADOV101
434300APPY ALBALI PET 1 LT1180103ZAF4155255867625740RAZ MARKET XIRDALAN BONUS300ZAUR BAYRVQSSAMIR ISMAYOV101
641500SE TEA MANGO-CAMOMMILE P0.51180103ZAF4155303628149120MGA MARKET QARACUXUR300ZAUR BRAMOVQKORKHAN SADOV404
434300APPY ALBALI PET 1 LT1180103ZAF4155278017541912ESCO-3 ASAN XIDMET300ZAUR BAYRVQSSAMIR ISMAYOV101
641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF415531921855840MONO-3300ZAUR BAYMOVQKORKHAN SADOV101
101800OLA QUTU 250ML 1X241180103ZAF415522771522820ADAG320HABIL SEEROVQQBAXTIYAR RUAMOV84084
929700NTA DUSES PET 0.5LX121180103ZAF4155221017683518STORE BABEK PROSPEKTI300ZAUR BAYMOVQKORAN SADOV202

<tbody>
</tbody>

List4
152224
152152
152556
152650
152226
152912
152672
152631
152258
152632
152333
152630
152836
152612
152922
152920
152554
152417
152181
152419
152237
152282
152596
152720
152605
152997
152678
152430
152067
152987
152895

<tbody>
</tbody>

Don't even know what i'v just posted))

BTW sorry for Mrs

So i need to count if list E:E ( list3 ) is not on list4 at the same time M:M > 0
 
Last edited:
Upvote 0
In that list col M is all 0, so there is nothing to sum.
 
Upvote 0
List is 500k row and M:M is my mistake it should be L:L

BTW list4 is in different sheet ("sheet4")
 
Last edited:
Upvote 0
With the data like

Excel 2013/2016
ABCDEFGHIJKLMN
1434300ALBALI PET 1 LT1180103ZAF41552095276256521MARKET BAKIXANOV RAZIN 1300ZAUR YRAMOVQKKHAN SADIQOV202
2101900OLA PET 1 LT1180103ZAF415534991525560ZABRAT BAYISI320HABIL FTEROVQQXTIYAR RUSTAMOV2800280
3434300APPY ALBALI PET 1 LT1180103ZAF4155330957625690SAR M-T RAZIN300ZAUR BAAMOVQKOHAN SADIQOV303
4434300APPY ALBALI PET 1 LT1180103ZAF4155214567625620SAR MARKET AXUNDOV300ZAUR BAYMOVQSSAIR ISMAYILOV505
5434300APPY ALBALI PET 1 LT1180103ZAF415528971856910DAUD MARKET BILECERI300ZAUR BAAMOVQSSMIR ISMAYILOV111
6929700FANTA DUSES PET 0.5LX121180103ZAF415526401778552ESCO-1 JALE PLAZA300ZAUR BAYMOVQSSIR ISMAYILOV222
7434300CAPPY ALBALI PET 1 LT1180103ZAF415526571778552ESCO-1 JALE PLAZA300ZAUR BAAMOVQSAMIR ISMAYILOV131
8641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF415529211774070HIPPER MARKET300ZAUR BAAMOVQSMIR ISMAYILOV141
9671200USE TEA QARPIZ PET 0,5LT1180103ZAF415529211774070HIPPER MARKET300ZAUR BAAMOVQSSAR ISMAYILOV101
10434300APPY ALBALI PET 1 LT1180103ZAF41552202476257221MARKET GENCE PROSPEKTI300UR BAYRAMOVQKOHAN SADIQOV202
11671200USE TEA QARPIZ PET 0,5LT1180103ZAF415528141797770BEEQROS MARKET300ZAUR BRAMOVQSSAM ISMAYILOV303
12434300APPY ALBALI PET 1 LT1180103ZAF4155229318189350AKRO MARKET MONTIN300ZAUR BRAMOQKORKHAN SAQOV808
13641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF4155262447625700RAZ MARKET 6 MKR LIBRA300UR BAYRAMOVQSSAMIR ISMAYOV202
14671200SE TEA QARPIZ PET 0,5LT1180103ZAF4155317618149170EGA MARKET INQLAB300ZAUR BAYMOVQSSAMIR ISMILOV202
15641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF4155268117669921RAZ MARKET OLIMPIKSTAR300ZAUR BAAMOVQSSAMIR ISMAYOV303
16929700NTA DUSES PET 0.5LX121180103ZAF4155268117669921RAZ MARKET OLIMPIKSTAR300ZAUR BAYROVQSSAMIR ISMAYOV101
17434300APPY ALBALI PET 1 LT1180103ZAF41552103176835418STORE MARKET AYGUN CITY300ZAUR BAAMOVQKORKHAN SADOV505
18929700NTA DUSES PET 0.5LX121180103ZAF41552111176835418STORE MARKET AYGUN CITY300ZR BAYROVQKORKHAN SADOV101
19434300APPY ALBALI PET 1 LT1180103ZAF4155255867625740RAZ MARKET XIRDALAN BONUS300ZAUR BAYRVQSSAMIR ISMAYOV101
20641500SE TEA MANGO-CAMOMMILE P0.51180103ZAF4155303628149120MGA MARKET QARACUXUR300ZAUR BRAMOVQKORKHAN SADOV404
21434300APPY ALBALI PET 1 LT1180103ZAF4155278017541912ESCO-3 ASAN XIDMET300ZAUR BAYRVQSSAMIR ISMAYOV101
22641500USE TEA MANGO-CAMOMMILE P0.51180103ZAF415531921855840MONO-3300ZAUR BAYMOVQKORKHAN SADOV101
23101800OLA QUTU 250ML 1X241180103ZAF415522771522820ADAG320HABIL SEEROVQQBAXTIYAR RUAMOV84084
24929700NTA DUSES PET 0.5LX121180103ZAF4155221017683518STORE BABEK PROSPEKTI300ZAUR BAYMOVQKORAN SADOV202
data


And sheet 4 like

Excel 2013/2016
CAP
1434300152224
2101900152152
3434300152556
4434300152650
5434300152226
6929700152912
7434300152672
8641500152631
9671200152258
10434300152632
11152333
12152630
13152836
14152612
15152922
16152920
17152554
18152417
19152181
20152419
21152237
22152282
23152596
24152720
25152605
26152997
27152678
28152430
29152067
30152987
31152895
sheet4


What is your expected answer?
 
Upvote 0
If C <> AP
L:L>0
A:A is not blank
sum L:L
i need to sum all excepting AP:AP
 
Last edited:
Upvote 0
You code is looking to sum col M not L, maybe that is the problem.
If not please answer my question.
 
Upvote 0
I get error
#N/A

<tbody>
</tbody>

Code:
Sub aqua()
    Dim Markets As Worksheet
    Set Markets = Sheets("sheet4")
    Sheets("DATA").Range("A:A").Name = "Urun"
    Sheets("DATA").Range("L:L").Name = "Sifaris"
    Sheets("DATA").Range("M:M").Name = "Printed"
    Sheets("DATA").Range("E:E").Name = "Musteri"
    Markets.Range("c1:c20").Name = "MARKET"
    Markets.Range("ap1:ap31").Name = "bayi"
    Sheets("DATA").Cells(5, "V") = [sum(if((isnumber(match(urun,market,0)))*(sifaris>0)*(urun<>"")*(musteri<>bayi),printed))]
End Sub

L:L are orders and M:M successfully sent orders and most of products are sold succesfully
 
Last edited:
Upvote 0
Try this
Code:
Sub Bonaqua()
    Dim Markets As Worksheet
    Set Markets = Sheets("sheet4")
    Sheets("DATA").Range("A:A").Name = "list1"
    Sheets("DATA").Range("L:L").Name = "list2"
    Sheets("DATA").Range("M:M").Name = "Printed"
    Sheets("DATA").Range("E:E").Name = "list3"
    Sheets("sheet4").Range("AP:AP").Name = "list4"
    Markets.Range("c1:c20").Name = "MARKET"
    Sheets("DATA").Cells(4, "V") = [sum(if((isnumber(match(list1,market,0)))*(list2>0)*(list1<>"")*(not(isnumber(match(list3,list4,0)))),printed))]
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
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