CountIF to Sumproduct

[Bandito1]

Hi all,

I use this formula to see the files that came in on a specific date;

=IF(B20="";"";COUNTIF('QA tracker data'!$I:$I;B20))

I notice now that when i close the source file (QA tracker data will be in different worksheet) that the data disappears.
I know the cause of this, because of COUNTIF

I want to turn the formula in SUMPRODUCT so the data stays when i close the other worksheet.

But when i change COUNTIF into SUMPRODUCT i get #Value error
Someone knows why?

Dashboard.xlsm
	B	C
20	5-okt-22	10
21	6-okt-22	#VALUE!
Sheet2

Cell Formulas
Range	Formula
B20:B21	B20	=IF(B19="","",IF(B19=EOMONTH(B19,0),"",$C$1+ROWS($1:5)-1))
C20	C20	=IF(B20="","",COUNTIF('QA tracker data'!$I:$I,B20))
C21	C21	=IF(B21="","",SUMPRODUCT('QA tracker data'!$I:$I,B21))

 

[Fluff]

Try it like

=IF(B21="","",SUMPRODUCT(--('QA tracker data'!$I:$I,B21)))

 

[Bandito1]

Tried, but the result didn't change

Dashboard.xlsm
	A	B
17	14-Jan-22	1
18	15-Jan-22	#VALUE!
BMR's in kast

Cell Formulas
Range	Formula
A17:A18	A17	=$A$3+ROWS($1:14)-1
B17	B17	=IF(A17="","",COUNTIF('QA tracker data'!I:I,A17))
B18	B18	=IF(A18="","",SUMPRODUCT(--('QA tracker data'!$I:$I,A18)))

Dashboard.xlsm
	I
3	Date in
4	14-jan-22
5	15-jan-22
6	15-jan-22
QA tracker data

 

[Fluff]

Oops, it should be

=IF(B21="","",SUMPRODUCT(--('QA tracker data'!$I:$I=B21)))

 

[Bandito1]

That is it
Should've been able to find that myself..

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.