Excel - Counting unique values that meet multiple criteria -

-

i'm trying use function count number of unique cells in spreadsheet that, @ same time, meet multiple criteria.

given following example:

a        b       c quant    store#  product 1        75012   banana 5                orange 6        56089   orange 3        89247   orange 7        45321   orange 2                apple 4        45321   apple

in example above, need know how many unique stores valid store# have received oranges or apples. in case above, result should 3 (stores 56089, 89247 , 45321).

this how started try solving problem:

=sum(if(frequency(b2:b9,b2:b9)>0,1))

the above formula yield number of unique stores valid store#, not ones have received oranges or apples. how can add criteria?

mine done different way (and works), main reason posting because wondering if second part of formula somehow made array formula making shorter.

to this, sort 3 columns on column b smallest largest , since part of array formula, type in formula , [ctrl]+[shift]+[enter]. sorted data looks this:

      b       c quant   store#  product   7   45321   orange   4   45321   apple   6   56089   orange   1   75012   banana   3   89247   orange   5           orange   2           apple

and formula is:

={sum((b3:b9>0)*(c3:c9="orange"))+sum((b3:b9>0)*(c3:c9="apple"))}-{if(and(or(c3="orange",c3="apple"),or(c4="orange",c4="apple"),b3=b4),1,0)+if(and(or(c4="orange",c4="apple"),or(c5="orange",c5="apple"),b4=b5),1,0)+if(and(or(c5="orange",c5="apple"),or(c6="orange",c6="apple"),b5=b6),1,0)+if(and(or(c6="orange",c6="apple"),or(c7="orange",c7="apple"),b6=b7),1,0)}

the first part (in curly braces) pretty self-explanatory:

sum((b3:b9>0)*(c3:c9="orange"))+sum((b3:b9>0)*(c3:c9="apple"))

what interested in trying see if second part can expressed array formula. had come with

=sum((and((c3:c9="orange"),or(c4:c9="orange",c4:c9="apple")))*(b3:b9=b4:b10)*(b3:b9<>""))

but seems counting "banana" legit entry reason. therefore, had go entering elements in 2nd part manually rather use array formula. have ideas how done?


Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more