=COUNTIF returning zero value - perhaps date format error??

[redspanna]

Hi all

I have a simple Data table and I'd like a formula to check down column F (Sales) for any value greater than Zero if the date in column H (Column2) matches the date held in cell ref J2

I have tried
=COUNTIFS(Data_Table[[#Headers],[SALES]], ">0", Data_Table[[#Headers],[Column2]], "="&J2)
but the retuen value is 0 in cell I2, where I would expect it to be 6 given the example data here..

NDA.v14.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	MONTH	YEAR	YEAR2	Q	SALES	DAY	Column2	Column3	0
2	01-Jan-20	Jan	2019-20	2020	Q1		Wed	January 2020	0	January 2020
3	02-Jan-20	Jan	2019-20	2020	Q1	140	Thu	January 2020
4	03-Jan-20	Jan	2019-20	2020	Q1		Fri	January 2020
5	04-Jan-20	Jan	2019-20	2020	Q1	70	Sat	January 2020
6	05-Jan-20	Jan	2019-20	2020	Q1	70	Sun	January 2020
7	06-Jan-20	Jan	2019-20	2020	Q1	960	Mon	January 2020
8	07-Jan-20	Jan	2019-20	2020	Q1	2000	Tue	January 2020
9	08-Jan-20	Jan	2019-20	2020	Q1		Wed	January 2020
10	09-Jan-20	Jan	2019-20	2020	Q1	70	Thu	January 2020
Data

Cell Formulas
Range	Formula
J1	J1	=COUNTIFS(Data_Table[[#Headers],[SALES]], ">0", Data_Table[[#Headers],[Column2]], ">"&J3)
B2:B10	B2	=IF(ISBLANK(A2),"",TEXT([@DATE],"mmm"))
C2:C10	C2	=IF(ISBLANK(A2),"",YEAR(EOMONTH(A2,9))-1&"-"&RIGHT(YEAR(EOMONTH(A2,9)),2))
D2:D10	D2	=IF(ISBLANK(A2),"",YEAR(A2))
E2:E10	E2	=IF(ISBLANK(A2),"",VLOOKUP(B2,Workings!$A$4:$C$15,3,FALSE))
G2:G10	G2	=IF(ISBLANK(A2),"",TEXT(A2,"DDD"))
H2:H10	H2	=IF(ISBLANK(A2),"",A2)
I2	I2	=COUNTIFS(Data_Table[[#Headers],[SALES]], ">0", Data_Table[[#Headers],[Column2]], ">"&J2)

I'm guessing its something to do perhaps with the way that column H and J2 is formatted to give the value as mmmm yyyy but can't figure it out

Appreciate any help

 

[RoryA]

You're looking at the header row. Try:

=COUNTIFS(Data_Table[SALES], ">0", Data_Table[Column2], ">"&J2)

 

[redspanna]

Thanks @RoryA for your help
However I'm still not getting the correct/expected results

As you can see the updated formula in I2 is giving the result as 9 where as I'd expect it to be 7 as this is the number of times a value of more than ZERO and the month is the same as that in J2 is matched. The formula seems to be counting the data in rows 12 & 13 even though the dates are not January 2020

NDA.v14.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	MONTH	YEAR	YEAR2	Q	SALES	DAY	Column2	Column3	Column4
2	01-Jan-20							January 2020	9	January 2020
3	02-Jan-20					140		January 2020
4	03-Jan-20							January 2020
5	04-Jan-20					70		January 2020
6	05-Jan-20					70		January 2020
7	06-Jan-20					960		January 2020
8	07-Jan-20					2000		January 2020
9	08-Jan-20							January 2020
10	09-Jan-20					70		January 2020
11	10-Jan-20					475		January 2020
12	03-Mar-22					50		March 2022
13	05-Sep-22					25		September 2022
Data (2)

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS([SALES], ">0", [Column2], ">"&$H$2)
H2:H13	H2	=IF(ISBLANK(A2),"",A2)

Thanks again though for your help

 

[RoryA]

You used > rather than = for the month criterion

 

[redspanna]

You used > rather than = for the month criterion

OK, then thats changed I get a ZERO retun

NDA.v14.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	MONTH	YEAR	YEAR2	Q	SALES	DAY	Column2	Column3	Column4
2	01-Jan-20							January 2020	0	January 2020
3	02-Jan-20					140		January 2020
4	03-Jan-20							January 2020
5	04-Jan-20					70		January 2020
6	05-Jan-20					70		January 2020
7	06-Jan-20					960		January 2020
8	07-Jan-20					2000		January 2020
9	08-Jan-20							January 2020
10	09-Jan-20					70		January 2020
11	10-Jan-20					475		January 2020
12	03-Mar-22					50		March 2022
13	05-Sep-22					25		September 2022
Data (2)

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS([SALES], ">0", [Column2], "="&$J$2)
H2:H13	H2	=IF(ISBLANK(A2),"",A2)

 

[RoryA]

I suspect the value in J2 is 1st Jan which only matches one of the rows of data, and that one has no sales. If you want the entire month, you'll need to amend Column2 and J2 to use the same text representation of the month, or convert all dates to 1st of the respective month.

 

[redspanna]

I suspect the value in J2 is 1st Jan which only matches one of the rows of data, and that one has no sales. If you want the entire month, you'll need to amend Column2 and J2 to use the same text representation of the month, or convert all dates to 1st of the respective month.

Yehh, as I suspected, it was a formating issue....
I've changed the format to match on several cells and its now working as expected (I think )

Many thanks for your help