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.

		04-Sep-17	05-Sep-17	06-Sep-17	Formula Required	Result required
Sr No.	Name	Log In	Log Out	Log In	Log Out	Log In	Log Out	Pickup	Drop	Pickup	Drop
1	A	Own Transport	Own Transport	Own Transport	Own Transport	Own Transport	Own Transport			0	0
2	B	Leave	Leave	Leave	Leave	Own Transport	19:00			0	1
3	C	10:00	19:00	10:00	19:00	10:00	19:00			3	3
4	D	10:00	19:00	10:00	19:00	10:00	19:00			3	3
5	E	12:30	21:30	12:30	21:30	10:00	19:00			3	3
6	F	12:30	21:30	Own Transport	Own Transport	Own Transport	Own Transport			1	1
7	G	12:30	21:30	Own Transport	Own Transport	Own Transport	Own Transport			1	1

<tbody>
</tbody><colgroup><col span="2"><col span="6"><col span="4"></colgroup>

 

[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))))))