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

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
  - John Triebe
    Copper Contributor
    Aug 27, 2018
    Thanks, that did the trick. It is interesting that the COUNTIFS function does not like criteria references to blank cells, but the SUMPRODUCT function can handle that. I'm unsure why but perhaps it is one of those "undocumented" features.