SOLVED

COUNTIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-2164681%22%20slang%3D%22en-US%22%3ECOUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2164681%22%20slang%3D%22en-US%22%3E%3CP%3EI%20keep%20getting%20a%20%23VALUE%20error%20with%20my%20countifs%20function%2C%20but%20I%20can't%20figure%20out%20why.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(G1%3AG12%2C%22%26gt%3B%3D45%22%2CH1%3AH6%2C%22%26gt%3B%3D55%22)%3C%2FP%3E%3CP%3EIt's%20a%20pretty%20simple%20function%20only%20containing%20numbers%20in%20the%20cells.%20The%20function%20works%20with%20each%20one%20alone%2C%20but%20not%20together.%20Can%20anyone%20tell%20me%20what%20is%20going%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2164681%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2164782%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2164782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978134%22%20target%3D%22_blank%22%3E%40aralston3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EYou%20need%20to%20correct%20the%20cell%20references%20in%20the%20formula%2C%20and%20it%20should%20like%2C%2C%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3DCOUNTIFS(G1%3AG12%2C%22%26gt%3B%3D45%22%2CH1%3AH12%2C%22%26gt%3B%3D55%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20size%3D%224%22%3EBecause%20the%20COUNTIFS%26nbsp%3Bneeds%20each%20of%20the%20criteria%20ranges%20to%20have%20the%20same%20size.%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EYour%20first%20criteria%20range%20has%2012%20ROWS%2C%20but%20another%20has%20only%206%2C%20so%20that%20you%20are%20getting%20an%20error.%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EEven%20after%20the%20suggested%20correction%20you%20may%20get%20either%20ZERO%20to%20unexpected%20result%2C%20juts%20because%20non%20of%20the%20criterion%20may%20not%20meeting%20the%20data%20in%20used%20data%20range.%3C%2FLI%3E%3CLI%3EI%20this%20situation%20better%20share%20some%20sample%20data%20wit%20us.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I keep getting a #VALUE error with my countifs function, but I can't figure out why. 

=COUNTIFS(G1:G12,">=45",H1:H6,">=55")

It's a pretty simple function only containing numbers in the cells. The function works with each one alone, but not together. Can anyone tell me what is going wrong?

2 Replies
best response confirmed by aralston3 (Occasional Visitor)
Solution

@aralston3 

You need to correct the cell references in the formula, and it should like,,,

 

=COUNTIFS(G1:G12,">=45",H1:H12,">=55")

 

  • Because the COUNTIFS needs each of the criteria ranges to have the same size.
  • Your first criteria range has 12 ROWS, but another has only 6, so that you are getting an error. 

N.B.

  • Even after the suggested correction you may get either ZERO to unexpected result, juts because non of the criterion may not meeting the data in used data range.
  • I this situation better share some sample data wit us.

 

Glad to help you,,, please keep asking ☺