SUMPRODUCT does not equal

[Newport Count]

Hi, i have a few formula for SUMPRODUCT that i gained from this site

all of them are eg ="order" but how do i use this formula to count all values that are not "order" - i.e. does not equal "order"

i have tried a couple of variations but dont seem to get anywhere! im sure this will be a simple solution too!

Thanks for all your help!

Rich

 

[Domski]

Try:

<>"order"

Dom

 

[Aladin Akyurek]

Hi, i have a few formula for SUMPRODUCT that i gained from this site

all of them are eg ="order" but how do i use this formula to count all values that are not "order" - i.e. does not equal "order"

i have tried a couple of variations but dont seem to get anywhere! im sure this will be a simple solution too!

Thanks for all your help!

Rich

<>"Order"

If this not help, try to post the formula you have.

 

[Newport Count]

Thanks both, yes i did try that one but i think because of the rest of the formula it isnt working - formula as follows:

=IF(H$2='Sheet 1'!$A$2,SUMPRODUCT(--('Sheet 1'!$L$11:$L$10000="Order"),--('Sheet 1'!$E$11:$E$10000="Pricing Block"),--('Sheet 1'!$K$11:$K$10000="Fully delivered")),"")

this is the original formula...but in column K the values can be "Fully delivered" or "Not delivered" or "Partially delivered"

currently it is only counting "Fully delivered" but i require the formula to also count "Partially delivered" also

Thanks again!

 

[Aladin Akyurek]

Thanks both, yes i did try that one but i think because of the rest of the formula it isnt working - formula as follows:

=IF(H$2='Sheet 1'!$A$2,SUMPRODUCT(--('Sheet 1'!$L$11:$L$10000="Order"),--('Sheet 1'!$E$11:$E$10000="Pricing Block"),--('Sheet 1'!$K$11:$K$10000="Fully delivered")),"")

this is the original formula...but in column K the values can be "Fully delivered" or "Not delivered" or "Partially delivered"

currently it is only counting "Fully delivered" but i require the formula to also count "Partially delivered" also

Thanks again!

Try...

=IF(H$2='Sheet 1'!$A$2,
   SUMPRODUCT(
     --('Sheet 1'!$L$11:$L$10000="Order"),
     --('Sheet 1'!$E$11:$E$10000="Pricing Block"),
     --ISNUMBER(MATCH('Sheet 1'!$K$11:$K$10000,
         {"Fully delivered","Partially delivered"},0))),
   "")

 

[Newport Count]

Yes - works perfectly thanks

but will it work if there are only "Fully delivered" or only "Partially delivered" in the column

or does it onyl work with both?

actually i can test this myself - will get back to you if any problems

Thanks