Forum Discussion
Using COUNTIFS and Blank Criteria
I'm using COUNTIFS to tabulate test results for my 4th grade math students, and want to summarize results on a recent test that I gave them. I have 4 types of criteria (Operation, Word Problems, Decimals and Response) and for each of the 50 questions I gave them there will be an entry into one or more of those criteria. COUNTIFS does the job, but to simplify the process, I would just like to create a criteria table and use COUNTIFS to tell me how many correct results there were for each grouping of criteria. This works for those cases where there are entries in each criteria, but if there is a blank cell, I must go into the COUNTIFS function and replace the criteria cell reference with "". Is there a way around this?
- Hi John Triebe,Enter the below Array Formula in cell M5. It will replace zero with blank=IF(SUM(IF(B$4:B$17=H5,1,0)*IF(C$4:C$17=I5,1,0)*IF(D$4:D$17=J5,1,0)*IF(E$4:E$17=K5,1,0))=0,"",SUM(IF(B$4:B$17=H5,1,0)*IF(C$4:C$17=I5,1,0)*IF(D$4:D$17=J5,1,0)*IF(E$4:E$17=K5,1,0)))
Or else, use the below Formula and change the Number Format of the cell to display zero as blank.=SUM(IF(B$4:B$17=H5,1,0)*IF(C$4:C$17=I5,1,0)*IF(D$4:D$17=J5,1,0)*IF(E$4:E$17=K5,1,0)Home > Format Cells > Number> Custom > Type > #;-#;"";@Notes:1. Array Formulas are entered using Control + Shift + Enter, instead of Enter.2. In the File which you had attached, you have you different types of formulas, in a column, based on the data, instead of only one formula. Using multiple formulas in the same column is not amongst the best practices in Excel, particularly when there is a possibility they may need to be dragged down.Do let me know if your problem is solved, and which formula you eventually used. It may help others on the forum.Vijaykumar Shetye,Panaji, Goa, India- Hi Vijaykumar,Thank you for the response. Perhaps I didn't make myself clear in my explanation. I know I have different types of formulas in the M column, and that is the problem. What I'm, trying to accomplish is to summarize how many of the entries in this small database (B3:E17) meet the criteria in each of the Rows (H5:K13) and show the results in Column (M5:M13). The numbers shown in the Result column are the correct results, and I would like to accomplish that by putting a formula in Cell M5 and copying it down. However, when I replace the ""'s in the formula in M5 with the cell references I5 and J5 the result in M5 becomes 0 which is not the correct answer (note that Row's 4 and 8 both meet the criteria of an A in the Operator column, blank in the Word and Decimal column, and C in the Response column). So my question is, how can I put a formula into M5 that refers to the criteria shown in H5:K5 and provides the correct result, and that will enable me to copy that same formula into M6:M13 to provide the correct results there as well. The only way I've been able to accomplish that is by replacing the criteria cell references that point to a "blank" cell with "". It seems that when you use a criteria in the COUNTIFS statement that refers to a blank cell, it provides a "0" result.I hope this better explains my dilemma. Thanks in advance for your attention on this matter.Sincerely.
John TriebeHi John Triebe,
The original File that you had sent, contains formulas which I believe have evaluated correct results. If these are not the correct results, then enter the correct results on another column, with reasons why the existing results are incorrect.
Have you tested the formulas that I had sent? Is there any difference from the results of these formulas and the expected results? Can you mention the details?
You may reattach the file, highlighting the cells where the results are not as per your expectations.
Vijaykumar Shetye,
Panaji, Goa, India
