Count Qty of Cells with a value in

[Steve Swift]

Hi all,

I'm sure this should be easy but I can't figure out how and I hope someone can help me out.

I have Column C that contains names of sales staff and column H that contains the relevant commision for that member of staff. So for example

C1 = Amanda H1 = 10.00
C2 = Carol H2 = 11.00
C3 = Amanda H3 = 12.00
C4 = Amanda H4 = 0.00

So I already calculate the commission amount with a SUMIF (C:C,E32,H:H) where E32 is the ref cell for the name Amanda.

What I need to show as well is the number of cells that contain a value greater than 0 against Amanda name. In my example above I would be expecting the result

Amanda = 2
Carol = 1



Thanks in advance.

Steve

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(C1:C4="Amanda"),--(H1:H4>0))

 

[Steve Swift]

Hi,

Thanks for that it works perfect over just those cells but is there any way I can make it look up the whole column C:C & H:H which seems the best option as it varies daily as more items are added. Currently for example I have 24 rows in the sheet at the moment so this works

=SUMPRODUCT(--(C1:C24="Amanda"),--(H1:H24>0))

but this doesn't

=SUMPRODUCT(--(C:C="Amanda"),--(H:H>0))

Thanks again

Steve

 

[fairwinds]

You cannot use entire column references like C:C in this formula.

You could however:

C1:C65535

 

[sbeatton]

Try

=SUMPRODUCT(--(C1:C65536="Amanda"),--(H1:H65536>0))

65536 is the maximum number of rows per worksheet

 

[fairwinds]

Try

=SUMPRODUCT(--(C1:C65536="Amanda"),--(H1:H65536>0))

65536 is the maximum number of rows per worksheet

Unfortunately, in some verions that formula automaticly changes into =SUMPRODUCT(--(C:C="Amanda"),--(H:H>0)) and thus error out.

Thus my suggestion above.

 

[Steve Swift]

Thanks very much,

Had to use the 65535 as did experience the problem with 65536


It works

Best Regards

Steve