Google Docs - Help with equivalent to COUNTIFS

First off I am pretty sure that the COUNTIFS formula is not available in Docs which is a real shame because I use it all the time for multiple reports. Basically, I have a grid that is updated every day by a department. We are trying to track certain performance stats. Anyway, the only way I have been able to do this is by creating multiple pivot tables and then to have my formulas reference them using INDEX and MATCH. The problem I have is that this is just too many tabs and it gets confusing for other users. I need this too be very simple. On Excel I have a "Start Date" cell, an "End Date" cell, an "Employee" cell and so on. The user simply has to fill these in to get any statistics he needs. On Docs they have to go to each Pivot Table, put in the date filters, make sure they all have the same date range and only then can they get, for instance, last weeks stats. I keep the date filters out because it updates real time when they are not there and that is nice to have. I would like to keep that. In other words, if the start and end date cells are left empty, it will just show the stats month to date as these grids are all done monthly. Here is an example of one of the formulas I have to use. Again, I would prefer that all formulas would reference the main raw data table and not a pivot but I have not found an efficient way to do that yet.

=IFERROR(INDEX(PreCallPivot!$A$1:$Z$98,MATCH("Connectivity Available",PreCallPivot!$A:$A,0),MATCH(B$1,PreCallPivot!$1:$1,0)),0)+IFERROR(INDEX(PreCallPivot!$A$1:$Z$98,MATCH("No Connectivity Available",PreCallPivot!$A:$A,0),MATCH(B$1,PreCallPivot!$1:$1,0)),0)+IFERROR(INDEX(PreCallPivot!$A$1:$Z$98,MATCH("Follow Up-Getting IP Within 7 Days",PreCallPivot!$A:$A,0),MATCH(B$1,PreCallPivot!$1:$1,0)),0)-IFERROR(INDEX(PrecallPivot!$A$1:$Z$98,MATCH("Do Not Contact-Not Completed",PreCallPivot!$A:$A,0),(MATCH(B$1,PreCallPivot!$1:$1,0)),0))

B1 is the employee's name.

I know there has to be an easier way to do this. If more information is needed, please let me know and I can provide whatever will help. Thanks in advance for any assistance.

Have you looked at using SUMPRODUCT? This can provide the same results as COUNTIFS, and is available in Google Docs (so I was informed this morning - I don't use it myself).

If you give me the COUNTIFS formula that you would like to use, it should be relatively easy to convert it into SP form.

Hope this helps.

Pete

=COUNTIFS($A:$A,">="&$A$26,$A:$A,"<="&$A$27,$J:$J,$A$28,$I:$I,"Connectivity Available")

This is basically it. I think if I can learn how to convert this one, I can figure out the rest. There are a lot of them.

A26 is the start date
A27 is the end date
A28 is the employee

One more issue I just noticed is that I have two drop down entries, one being "Connectivity Available" and one being "No Connectivity Available." Because I have this formula searching for Connectivity Available, it is bring back results for both. Anyway to make sure it is an exact match?

Okay, try this as a replacement:

=SUMPRODUCT(($A:$A>=$A$26)*($A:$A<=$A$27)*($J:$J=$A$28)*($I:$I="Connectivity Available"))

Note the similarity with COUNTIFS - each term is in brackets separated by * (equivalent to AND for arrays) and the comparison is done within the brackets.

You may find this takes more time to execute as you are using full-column references (COUNTIFS is intelligent enough to look only through the used range), so it might be better to limit the ranges only to the rows that you actually use. In earlier versions of Excel (i.e. 2003 and earlier) you couldn't use full-column references with SP anyway, so I don't know if Google Docs also has this restriction.

This will look for an exact match in column I, but your COUNTIFS formula should also have looked only for an exact match as you didn't use a wildcard character.

Hope this helps.

Pete

=SUMPRODUCT((VIP!$A:$A>=$I$1)*(VIP!$A:$A<=$I$2)*(VIP!$J:$J=$I$3)*(VIP!$I:$I="Connectivity Available"))

This is what I put in but it is not working. Just to clarify here is what everything represents.

VIP is the raw data tab.
VIP column A is the date column.
VIP column J is where the employee names go.
VIP column I is where "Connectivity Available" or other choices go.
I1 is start date.
I2 is end date.
I3 is employee name.

It seems lie the formula is running. It does not return an error but, regardless of what I enter in to I1, I2 and I3, it always returns a value of 0. I know this is incorrect.

Perhaps your dates are really text values that just look like dates. Can you attach a sample (Excel) file?

Pete

Ha! I moved it to Excel and it worked. It's just not working on Docs. Weird.

Well, perhaps it is because of restrictions on ranges with SP in Google Docs. Instead of $A:$A, change it to $A$1:$A$500 (twice) and similar for the 3rd and 4th term, and see if that works.

If it doesn't, then there's not much more I can do, as I don't use Google Docs.

Hope this helps.

Pete

Nope. I even changed it to the exact range so far which is 2 thru 196. This grid usually goes up to about 5000 lines by the end. It is still returning a value of 0 though for any combination of name and dates I put in. I wish I didn't use Google Docs either. I hate it. This would take me about 20 minutes on Excel. Oh well. Thanks for trying. I appreciate it. You wouldn't happen to know another cloud based free service that is more like Excel?

Originally Posted by livifivil

You wouldn't happen to know another cloud based free service that is more like Excel?

Sorry, no - I help out on an Excel Forum as that is what I know about.

Pete

Wow, I think that worked. Thank you so much. I just want to make sure I understand how this works though. Basically, I can use as many conditions as I want within the SUM parenthesis and enclose that in the ArrayFormula function to give me the result I am looking for?