Forum Discussion

Copper Contributor

Aug 25, 2018

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, Deci...

Test Results.xlsx11 KB

excel

Formulas and Functions

office 365

vijaykumar shetye

Brass Contributor

Aug 26, 2018

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

John Triebe

Copper Contributor

Aug 26, 2018

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 Triebe

vijaykumar shetye
Brass Contributor
Aug 26, 2018
Hi 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
- John Triebe
  Copper Contributor
  Aug 26, 2018
  Yes, I did try your method and it did not provide the correct result. I've attached another small spreadsheet showing the correct results (in green) and what I get (in RED) if I try using the COUNTIFS function in which one or more of the criteria reference cells contains a blank, and then copying that for multiple sets of criteria. I hope this better explains what I'm trying to accomplish. And I'll also ask that if the COUNTIFS function will not work in this situation, is there a statement using another function that could be copied to provide the correct results?
  Test Results.xlsx11 KB
  - SergeiBaklan
    Diamond Contributor
    Aug 27, 2018
    Hi John,
    
    I'd use
    
    =SUMPRODUCT(($B$4:$B$17=H5)*($C$4:$C$17=I5)*($D$4:$D$17=J5)*($E$4:$E$17=K5))
    
    and attached
    Test Results.xlsx12 KB