Count Function Help

Thank you in advance for any assistance you can provide:

Excel example.JPG

I am trying to count the number of clients (Row 22) accrued each month. I have tried various Count, Sum, Offset and Logic rules but cannot get this to work.

Thanks Sambo Kid.

Problem with that formula is it only counts the active clients in that month. If we assume a client becomes live the first month they sell units, any units they sell in a later month shouldn't be counted.

Sorry if I'm not explaining as well as poss!

FYI I have hard entered the count value that the formula needs to generate in Row 22

Originally Posted by KOKOSEK

Did I understand correctly? Ex. Client10 (in row 11) have to be counted only in D22 as 'his' debut and should not be taken into account on Apr and May, right?

Correct as once they have made unit sales I want to qualify them as an active client. Therefore they are counted only once in February '19.

Thank you

Originally Posted by KOKOSEK

So attached file is wrong imho, should be

Attachment 610567

3 7(4 new) 7(0)new 8(1 new) etc.

I may be wrong but when I manually total them I get Client 12 becoming active in July'19 and therefore it goes 10,11,12,12,13?

Try this in B22:

=SUMPRODUCT(--(SUMIF(OFFSET($B$2:B$2,ROW($B$2:$B$20)-ROWS($B$1:$B$2),),">0")>0))

Originally Posted by phuocam

try this in b22:

=sumproduct(--(sumif(offset($b$2:b$2,row($b$2:$b$20)-rows($b$1:$b$2),),">0")>0))

thank you so much!

Originally Posted by Phuocam

Try this in B22:

=SUMPRODUCT(--(SUMIF(OFFSET($B$2:B$2,ROW($B$2:$B$20)-ROWS($B$1:$B$2),),">0")>0))

Apologies if I am missing something obvious however when I apply this to a greater set of rows I can't get it to work. I've attached the the actual anonymous version for ease. The row the count needs to go in is at 119.

Thank you again

Originally Posted by Bo_Ry

Please try at J119

=SUMPRODUCT(N(MMULT(N($J31:J117>0),ROW(INDIRECT("1:"&COLUMNS($J31:J117)))^0)>0))

Excellent it works thank you.

Could you please explain the formula so I understand for future reference?

Originally Posted by Bo_Ry

First, you need to understand MMULT, which quite hard to explain.

Please read this

Small change
J119
=SUMPRODUCT(N(MMULT($J31:J117,ROW(INDIRECT("1:"&COLUMNS($J31:J117)))^0)>0))


J119
$J31:J117 return matrix of 87 Rows and 1 Column
ROW(INDIRECT("1:"&COLUMNS($J31:J117)))^0 give {1} 1 row 1 column
MMULT(N($J31:J117>0),ROW(INDIRECT("1:"&COLUMNS($J31:J117)))^0) give result of sum of each 87 rows

For MMULT Column# of the 1st array need to match row# of 2nd Array as in blue

K119
$J31:K117 return matrix of 87 Rows and 2 Columns
ROW(INDIRECT("1:"&COLUMNS($J31:K117)))^0 give {1;1} 2 rows 1 column
MMULT(N($J31:J117>0),ROW(INDIRECT("1:"&COLUMNS($J31:J117)))^0) give result of sum of each 87 rows and 2 columns, and so on for the next column.


=SUMPRODUCT(N(MMULT($J31:J117,ROW(INDIRECT("1:"&COLUMNS($J31:J117)))^0)>0))
Green part for chech each row if sum are more than 1 and Sumproduct to count that.



You may change Phuocam's formula to below.

Sumif(offset()) result is similar to MMULT()

=SUMPRODUCT(N(SUMIF(OFFSET($J31:J31,ROW(J31:J117)-ROW(J31),),">0")>0))

or

=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($J31:J31,ROW(J31:J117)-ROW(J31),))>0))

Thank you, really appreciate your help and tuition!