Forum Discussion
COUNTIFS and Multiple criteria?
You can do something like this using a Countifs() formula like this:
=COUNTIFS(I:I,"End User",J:J,"Added ",B:B,"MI")+COUNTIFS(I:I,"End User",J:J,"Added ",B:B,"IL")
You may be having trouble because the word Added in your spreadsheet has a trailing space on the end of it.... i.e. instead of "Added" the values are actually "Added "
Oh man, a simple space has had me confused for ages. Thanks for the help. I have now put these formulas into a separate table on the main page. Is there a way I can then cycle through these based on the date. For example, I would like to see what has been added, amended and reviewed on the 11th of June 2018.
It could be
=COUNTIFS(Sheet1!I:I,"End User",Sheet1!J:J,"Added ",Sheet1!B:B,"MI",Sheet1!N:N,$A$1)+ COUNTIFS(Sheet1!I:I,"End User",Sheet1!J:J,"Added ",Sheet1!B:B,"IL",Sheet1!N:N,$A$1)
if in A1 is June 11, 2018 in your locale format
The problem with that is I would have to change the date in the formula each time. I want to be able to cycle through easily. The best way I can explain it is I would like a drop down box to cycle through the date in Column N... and then each change of the date will change the return values in the table for the corresponding date.
Find attached my sample data sheet with the table I need to dynamically change the values as I change the date at the top.
Hi Shaun,
When you need to generate list of your dates first. As variant you may use
=IFERROR(LOOKUP(2,1/(COUNTIF($H$1:$H1,'New Data'!$N2:$N1000)=0)/('New Data'!$N2:$N1000<>""),'New Data'!$N2:$N1000),"")in H2 of the attached file, and drag it down far enough.
To exclude blanks from drop down list we add formula in Name Manager
='Main Page'!$H$2:INDEX('Main Page'!$H$2:$H$1000,SUMPRODUCT(--('Main Page'!$H$2:$H$1000<>"")))and let it give the name OurDates.
After that use that list as drop down one for A1.
Now we may add dates criteria for your COUNTIFS/SUMIFS formula, I did that for Added section.
As cosmetic, I unmerged cells in titles of your sections and format them as Center Across Selection. In general that's not a good idea to use merged cells, better to avoid where possible.
Other cosmetic could be not to hardcode in formulas states names and parameters but put them in some cells and use references on that cells. It will be much easier in maintenance.
