Count data in cells based on criteria

[VinodN]

Hi,

I am working on an excel wherein there is login and logout mentioned for employees. I am looking out for a formula which will count from row 1 all the time related cells and give me an output. For Eg. In row 2 the Pickup should be 0 and the Drop should be 1 (since there is time on 6/9/17 column for log out). I have mentioned the results required in the subsequent columns.

Request you to please help me with the formula.

Below is the table for your reference.

[TABLE="width: 878"]
<tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]04-Sep-17[/TD]
[TD="colspan: 2"]05-Sep-17[/TD]
[TD="colspan: 2"]06-Sep-17[/TD]
[TD="colspan: 2"]Formula Required[/TD]
[TD="colspan: 2"]Result required[/TD]
[/TR]
[TR]
[TD]Sr No.[/TD]
[TD]Name[/TD]
[TD]Log In[/TD]
[TD]Log Out[/TD]
[TD]Log In[/TD]
[TD]Log Out[/TD]
[TD]Log In[/TD]
[TD]Log Out[/TD]
[TD]Pickup[/TD]
[TD]Drop[/TD]
[TD]Pickup[/TD]
[TD]Drop[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]Leave[/TD]
[TD]Leave[/TD]
[TD]Leave[/TD]
[TD]Leave[/TD]
[TD]Own Transport[/TD]
[TD]19:00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E[/TD]
[TD]12:30[/TD]
[TD]21:30[/TD]
[TD]12:30[/TD]
[TD]21:30[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]F[/TD]
[TD]12:30[/TD]
[TD]21:30[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]G[/TD]
[TD]12:30[/TD]
[TD]21:30[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD]Own Transport[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="6"><col span="4"></colgroup>[/TABLE]

 

[Zubair Muhammad]

Hi Vinod,

Create a small user defined function to do this

Function CountNonText(ParamArray r()) As Integer
Dim i As Integer
For i = LBound(r) To UBound(r)
If Not WorksheetFunction.IsText(r(i)) Then
CountNonText = CountNonText + 1
End If
Next i
End Function

Then use this formula in Column Formula Required assuming Logins are in column C,E,G

=CountNonText(C4,E4,G4)

 

[istiasztalos]

Hi,

You can use ISTEXT or ISNUMBER default formulas, same as the VBA coded custom formula
=SUM(ISNUMBER(C4),ISNUMBER(E4),ISNUMBER(G4))

In your example you only have 3 days, but if you have a longer date period you can use this formula for any number of days (for example up to column Z), it will count the timestamps but only in every other column (this is an array formula, add with CTRL+SHIFT+ENTER)

=SUM(ISNUMBER(C4:Z4)*TRANSPOSE(ISODD(ROW(INDIRECT("1:"&COLUMNS(C4:Z4))))))