selection - Excel function and expression for counting only certain items included on an invoice -


i count number of "invoices" had sale of item (lets "abc" don't want count invoice if didn't have "abc" on sale in example below, invoice 3 not counted because "abc" not included on invoice); if did have "abc" on invoice, want count variations of size , color (for example may want count number of color yellow or size 12's; or number of invoices had size 11 , 21 on invoice)

could me function use in excel? , expression? i'm thinking countifs? i'm new excel, thanks!

customer      date    invoice item    size    color -------------.-------.-------.-------.-------.--------- me            1012014       1 abc          23 brown               1012014       2 abc          11 black               1012014       2 bqr          14 red               1012014       2 rpg          12 red     someoneelse   1022014       3 erp          12 yellow     someoneelse   1022014       3 rky          21 blue     them          1102014       4 abc          14 red   

assuming numeric invoices in c2:c10 , items in d2:d10 "array formula" give result 3 (different numbered invoices include item "abc")

=sum(if(frequency(if(d2:d10="abc",c2:c10),c2:c10),1))

confirmed ctrl+shift+enter

for "or" per comment can use + this

=sum(if(frequency(if((d2:d10="abc")+(d2:d10="cde")+(d2:d10="fff"),c2:c10),c2:c10),1))

or use match this:

=sum(if(frequency(if(isnumber(match(d2:d10,{"abc","cde","fff"},0)),c2:c10),c2:c10),1))

which can more extended 30 items - can list them out in formula or if recorded somewhere on spreadsheet, g1:g30 can use range in formula, i.e.

=sum(if(frequency(if(isnumber(match(d2:d10,g1:g30,0)),c2:c10),c2:c10),1))


Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -