Countifs that will not count formulas that return ""

[Liz_I3]

Hello
On sheet(Data) I have a Column of Name (G) and a Column of Dates (B). Column F is a concatenation of the 2 columns G&B ( Name and Date Joined to use as a lookup field)

On Sheet1 I have a Column of Dates (A) and Column of Names (B) I wanted a formula in Column C that would count the Number of Items on Sheet(Data) column K where on Sheet(Data) Column F matched the concatenation of Columns B&A on Sheet1.

I used this formula =COUNTIFS(DATA!F:F,B2&A2,DATA!K:K,"<>") but DataK is a formula that has iferror "" so my Countifs returns the count of the formulas not just if a value is returned (which is this case is a time) Is there a way to change this not to count the formula so if the formula is a blank or "" it is not counted

This formula work =SUMPRODUCT((DATA!K:K<>"")*(DATA!K:K<>0)) but it counts all the non "" cells in column K I could not get it to count just one persons name and particular date

I was trying the sumproduct =SUMPRODUCT(--(DATA!F2:F6000=B2&A2),DATA!K2:K6000) but keep getting NA

Thanks
L

 

[Fluff]

Try
=COUNTIFS(DATA!F:F,B2&A2,DATA!K:K,"><")

 

[Liz_I3]

That returned 0. Answer should be 8

Thanks for trying
L

 

[Fluff]

In that case can you please post some sample data using the XL2BB add-in?

 

[Liz_I3]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sheet1				Sheet(DATA)
2	Date	Name	Count of Column M (SheetDATA)		Message Date/Time	Date	Time	Card #	LKUP Value	Name	IN	Out	Time In
3	1/13/20	Kai	1		1/13/20 6:39:10 AM	1/13/20	6:39	293323	Kai43843	Kai	IN		6:39
4	1/13/20	John	1		1/13/20 3:01:11 PM	1/13/20	15:01	293323	Kai43843	Kai		OUT
5	1/13/20	Liz	1		1/14/20 3:47:13 PM	1/14/20	15:47	293323	Kai43844	Kai	IN		15:47
6	1/14/20	Kai	1		1/13/20 5:25:16 PM	1/13/20	17:25	293323	Kai43843	Kai		OUT
7	1/14/20	John	1		1/13/20 10:16:14 AM	1/13/20	10:16	276157	John43843	John	IN		10:16
8	1/14/20	Liz	1		1/13/20 10:17:20 AM	1/13/20	10:17	276157	John43843	John		OUT
9					1/14/20 10:20:23 AM	1/14/20	10:20	276157	John43844	John	IN		10:20
10					1/14/20 1:03:50 PM	1/14/20	13:03	276157	John43844	John		OUT
11					1/13/20 1:29:06 PM	1/13/20	13:29	276157	Liz43843	Liz	IN		13:29
12					1/13/20 1:35:07 PM	1/13/20	13:35	276157	Liz43843	Liz		OUT
13					1/14/20 1:37:54 PM	1/14/20	13:37	276157	Liz43844	Liz	IN		13:37
Sheet2

Cell Formulas
Range	Formula
F3:F13	F3	=INT(E3)
G3:G13	G3	=E3-F3
I3:I13	I3	=J3&F3
M3:M13	M3	=IF(K3="IN",G3,"")

 

[Fluff]

Which cells is the formula meant to be looking at? There are no IfError functions in what you've shown.

 

[Liz_I3]

Hi
Column M Time In. I want to count the number of times entered for each person. But the column is a formula, so it is counting each row including the formulas instead of just the values i.e. for Kai there are 2 entries for Jan 13/20 but he only has 1 Time In but any formulas I have return 2 what I want is 1.

Thanks
L

 

[Fluff]

In that case how about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sheet1				Sheet(DATA)
2	Date	Name	Count of Column M (SheetDATA)		Message Date/Time	Date	Time	Card #	LKUP Value	Name	IN	Out	Time In
3	13/01/2020	Kai	1		13/01/2020 06:39:10	43843	0.277199074	293323	Kai43843	Kai	IN		0.277199074
4	13/01/2020	John	1		13/01/2020 15:01:11	43843	0.625821759	293323	Kai43843	Kai		OUT
5	13/01/2020	Liz	1		14/01/2020 15:47:13	43844	0.657789352	293323	Kai43844	Kai	IN		0.657789352
6	14/01/2020	Kai	1		13/01/2020 17:25:16	43843	0.72587963	293323	Kai43843	Kai		OUT
7	14/01/2020	John	1		13/01/2020 10:16:14	43843	0.427939815	276157	John43843	John	IN		0.427939815
8	14/01/2020	Liz	1		13/01/2020 10:17:20	43843	0.428703704	276157	John43843	John		OUT
9					14/01/2020 10:20:23	43844	0.430821759	276157	John43844	John	IN		0.430821759
10					14/01/2020 13:03:50	43844	0.544328704	276157	John43844	John		OUT
11					13/01/2020 13:29:06	43843	0.561875	276157	Liz43843	Liz	IN		0.561875
12					13/01/2020 13:35:07	43843	0.566053241	276157	Liz43843	Liz		OUT
13					14/01/2020 13:37:54	43844	0.567986111	276157	Liz43844	Liz	IN		0.567986111
25

Cell Formulas
Range	Formula
F3:F13	F3	=INT(E3)
G3:G13	G3	=E3-F3
C3:C8	C3	=COUNTIFS(I:I,B3&A3,M:M,">0")
I3:I13	I3	=J3&F3
M3:M13	M3	=IF(K3="IN",G3,"")

 

[Liz_I3]

OMG That is fabulous...works perfect
Thank you so much...I played with it for hours

Liz

 

[Fluff]

You're welcome & thanks for the feedback