Advanced CountA?

[HomeTek]

Hi,

As you can see in the screen print below, I am using a simple formula to count the number of sports that have been played each day.

The problem is that some teams play more than 1 sport per day.

Can you think of a way I can either improve this formula so that It can tell when more than 1 sport has been entered into a cell or maybe there is a better way to re-design the worksheet to make it easier? I know I could add extra rows so that only 1 sports is allowed to be entered into a cell, but there could be as many as 10 sports played by each team per day and as it will be an ongoing calendar it will get very big very fast.

Many thanks

 

[mikerickson]

Your linked image isn't comming up.
But this formula will return the number of commas in A1:A10
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))

If when multiple sports have been played in a day, and there are mulitple sports entered into a cell with a comma between each sport the total number of sports in A1:A10 would be

=COUNTA(A1:A10)+SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))

 

[HomeTek]

Your linked image isn't comming up.
But this formula will return the number of commas in A1:A10
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))

If when multiple sports have been played in a day, and there are mulitple sports entered into a cell with a comma between each sport the total number of sports in A1:A10 would be

=COUNTA(A1:A10)+SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))

Thank you very much for this. Much appreciated. This works perfectly. It never even crossed my mind that this would be a way around it.

I'm not too sure why the picture didn't upload, but for completion I'll post it again.


EDIT: Off topic, but I've just noticed that the days say "Thirsday" on the left. That's a bit strange seeing as the only day I typed in was "Monday" and dragged the formula down. I'm pretty sure I have never altered the list in the options. Ah well. I better go and check on that. :D

 

[mikerickson]

Happy Satireday, with that layout the formula in F4 would be
=COUNTA(A4:E4)+SUMPRODUCT(LEN(A4:E4)-LEN(SUBSTITUTE(A4:E4,",","")))