PDA

View Full Version : Counting based on multiple criteria



mojo70115
12-08-2008, 03:38 PM
I have been trying to devise a formula to count entries in 1 column based on criteria in a 2nd column. I have 2 columns of data, product code (A) and month (B). I want to count each time a product code appears in a given month, but count it only once per month even if it appears multiple times in a given month. See below. The formula would need to count product 728 once in each month in which it is sold (once in month 1 and once in month 2). Likewise it would count 843 once in month 2 and once in each of months 3 and 4.

A B
728 1
728 1
843 2
728 2
843 3
843 3
843 4

Bob Phillips
12-08-2008, 04:07 PM
Try this array formula

=SUM(--(FREQUENCY(IF(A1:A100=728,MATCH(B1:B100,B1:B100,0)),ROW(INDIRECT("1:"&ROWS(B1:B100))))>0))

mojo70115
12-08-2008, 04:19 PM
Very nice. Thanks