sales log counting

[CHRISLEYENDECKER]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="align: left"]

totals

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L15158[/TD]
[TD]L15205-1[/TD]
[TD]9397[/TD]
[TD]L15221[/TD]
[TD]L15228[/TD]
[TD]HALF[/TD]
[TD]L13522[/TD]
[TD]5.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So we have a sales log that has different stock numbers, alpha numeric.


I currently use =SUMPRODUCT(COUNTIF(B9:K10,{"*";"HALF"}),{1;-0.5})

As you can see it will not add stock #9397 because it doesn't have any letters in it.


Any idea how I can fix this?

 

[gsistek]

You could try
=COUNTA(B1:K1)-COUNTIF(B1:K1,"HALF")/2

 

[CHRISLEYENDECKER]

You could try
=COUNTA(B1:K1)-COUNTIF(B1:K1,"HALF")/2

Wow that's sick! Thanks man.

Just a quick follow up question, some of the the sales guides sell cars together so we split their deals.

I would like to post the stock # under both of their names and count it has a half instead of having to delete it write the word "half" and add a comment box with the original info.


Example

Chris 9397 .5

Craig 9397 .5