COUNTIFS and Multiple criteria?

Question

I am not sure if the COUNTIFS function is the correct method I should be using, however, I am trying to count something based on multiple criteria.

I need to count or sum the number of End Users and ISV in the table Added in both MI and IL.

This needs to be repeated for Partners in the table Added in MI and IL.

Repeat for Amended, Repeat for Reviewed.

I then need the number of Contacts Added for End User and ISV in MI and IL.

Repeat for Amended, Repeat for Reviewed.

I would like these formulas to dynamically change based on Date e.g. Contacts Added 11 June 2018.

I have this data accessible in a pivot table, however, it is a lot of filtering to get the required numbers. I have included a filtered pivot table with an example of the data I need for End Users.

Much Appreciated if anyone can help :)

matt mickle · Answer

You can do something like this using a Countifs() formula like this:
&nbsp;
=COUNTIFS(I:I,"End User",J:J,"Added ",B:B,"MI")+COUNTIFS(I:I,"End User",J:J,"Added ",B:B,"IL")
&nbsp;
You may be having trouble because the word Added in your spreadsheet has a trailing space on the end of it....&nbsp; i.e. instead of "Added" the values are actually "Added "
&nbsp;
&nbsp;

shaun siddiqui · Answer

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.

sergeibaklan · Answer

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
&nbsp;

shaun siddiqui · Answer

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&nbsp;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&nbsp;date.&nbsp;&nbsp;&nbsp;Find attached my sample data sheet with the table I need to dynamically change the values as I change the date at the top.

sergeibaklan · Answer

Hi Shaun,
&nbsp;
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&lt;&gt;""),'New Data'!$N2:$N1000),"")
in H2 of the attached file, and drag it down far enough.
&nbsp;
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&lt;&gt;"")))
and let it give the name OurDates.
After that use that list as drop down one for A1.
&nbsp;
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.
&nbsp;
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&nbsp; easier in maintenance.
&nbsp;

Forum Discussion

COUNTIFS and Multiple criteria?

5 Replies

Resources