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 col R)
In this dataset, if I filter for these three items (some columns hidden), I get ONE available response on line 736 of data (sheet name - PS_excelFile) fulfilling the 3 requirements, shown here:
I am trying to replicate this exact filter extraction on a report page (below) with COUNTIFS, using this formula in cell E14 to find the three criteria: =COUNTIFS(PS_excelFile!$P$6:$P$10006,Sheet1!$A14,PS_excelFile!$R$6:$R$10006,"="&E$1,PS_excelFile!$C$6:$C$10006,"*"&$A$4&"*")
As you can see, this formula in E14 returns zero. More puzzling, pasting the formula works another person on the report page for March.
Obviously I can't trust the count right now. I have also tried using DATE in the formula to match the month, rather than comparing the date string to the header (dates usually being that which wrecks havoc with logic). Anyone see what it is that am I missing?
Thanks
Sirrobocop
- 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?
2 Replies
- 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 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
