Forum Discussion
Solved
COUNTIFS at odds with filtering
I have an exported data set that I'm trying to extract certain information from - looking for this criteria: specific name (in col P) specific mission name (in col C) matching specified month (in...
- I think it is the date. you said you tried using DATE in the formula but how did you set that up? Did you try something like:
=COUNTIFS(PS_excelFile!$P$6:$P$10006,Sheet1!$A14,PS_excelFile!$R$6:$R$10006,">="&DATEVALUE(E$1& " 1, "& $B$11), PS_excelFile!$R$6:$R$10006,"<="&EOMONTH(DATEVALUE(E$1& " 1, "& $B$11),0),PS_excelFile!$C$6:$C$10006,"*"&$A$4&"*")
basically did you do the countifs >= beginning of the month and <= to the end of the month?
I think it is the date. you said you tried using DATE in the formula but how did you set that up? Did you try something like:
=COUNTIFS(PS_excelFile!$P$6:$P$10006,Sheet1!$A14,PS_excelFile!$R$6:$R$10006,">="&DATEVALUE(E$1& " 1, "& $B$11), PS_excelFile!$R$6:$R$10006,"<="&EOMONTH(DATEVALUE(E$1& " 1, "& $B$11),0),PS_excelFile!$C$6:$C$10006,"*"&$A$4&"*")
basically did you do the countifs >= beginning of the month and <= to the end of the month?
=COUNTIFS(PS_excelFile!$P$6:$P$10006,Sheet1!$A14,PS_excelFile!$R$6:$R$10006,">="&DATEVALUE(E$1& " 1, "& $B$11), PS_excelFile!$R$6:$R$10006,"<="&EOMONTH(DATEVALUE(E$1& " 1, "& $B$11),0),PS_excelFile!$C$6:$C$10006,"*"&$A$4&"*")
basically did you do the countifs >= beginning of the month and <= to the end of the month?
I believe you are on to the problem. I actually only care about the month, and tried to set it up so that it only registers the first day of the month. However, the database doesn't have that limitation. I'll implement your range check and if it doesn't work, I'll post another update. Thanksmtarler !
sirrobocop