I am trying to create the Range of a CountIf formula, using formulas. I found some data on using INDIRECT, but it doesn't seem to fit the bill. Here is the specific problem:


   A     B   C   D  
1        Jan Feb Mar  
2  Bob   2   0   1  
3  Jane  
4  Joe


   A    B    C     D  
1       Blue Green Red  
2  Bob  Jan  Jan   Mar   
3  Jane  
4  Joe

I am trying to make a formula in Sheet 1 (cells B2, C2, D2, etc.) that will look up the name in the first column (take "Bob" for example), find the name in Sheet 2 column A, (i.e. find "Bob") and count all the cells in Bob's row that contain the name in 1st Row of Sheet 1 (Jan in the base of B1).

I tried different ways of using Vlookup and Indirect to feed a Range into the CountIf function.

Any ideas how to give a range into CountIf based on looking up a reference cell?

  • 1
    You're visualizing a particular approach, and the example probably seems adequate to illustrate that. But there are a lot of ways to accomplish your objective, and the example and explanation are too ambiguous for people to provide a good answer. Can you better explain the actual problem and what the expected result would be? In your example, it isn't clear how countif would apply to that, or what the variable range would be. For example, it looks like every row has a value in each column, so a solution could be to simply give the number of data columns. (cont'd) – fixer1234 Apr 10 '19 at 7:20
  • You can use indirect to define a data range, but you say that isn't what you need. – fixer1234 Apr 10 '19 at 7:20
  • Sorry for the ambiguity. What I am trying to accomplish is having a count of times a certain month comes up in each row in Sheet 2. So for instance, in Bob's row in sheet 2, Jan comes up 2 times. In sheet 1 I have a column for each month, so Bob/Jan cell should have a formula that calculates to 2. Also Sheet 2 may get sorted from time to time. So the calculation is sheet 1 needs to reference the row in sheet based on the name entered in the cell. – bhinojosa Apr 10 '19 at 17:23
  • But that's just counting based on a criterion (=Jan). That has nothing to do with setting or adjusting the range used. The range is where you look to find and count qualifying items. – fixer1234 Apr 10 '19 at 17:36
  • Where the range comes in is: In cell B2 (sheet 1) I want it to look in sheet 2 for the row which contains what is in A2 ("Bob") and count all instances of cells matching B1 ("Jan"). So the range needs to be the Row in Sheet 2 which starts with "Bob". So it is a variable range for lack of a better word. – bhinojosa Apr 10 '19 at 18:50

This can be solves by using SUMPRODUCT function:

enter image description here

How it works:

  • Enter this Formula in Cell B2 of Sheet 1 and fill Down then Right.


Adjust Cell references in the Formula as needed.

| improve this answer | |
  • Thank you. That formula gives the correct data in sheet 1. The only problem is that sheet 2 is sorted from time to time, and that throws off the data. The formula needs to reference the names entered in column A. Sorry I did not mention that. – bhinojosa Apr 10 '19 at 17:25
  • I don't see why it won't work. – Alex M Apr 10 '19 at 21:27
  • When I change the sequence of the names on Sheet 2 using sort, the values the formulas display on sheet 1 change. I think the second part of the formula (Shee2!$B2:$D2=B$1) only looks at a certain row number, and if the row moves than it doesn't follow it. – bhinojosa Apr 10 '19 at 22:36
  • @bhinojosa,, it's good that my solution works for you, it's not possible to rule out what's went wrong after data been Sorted unless the Sorted data in not available to analyze,,. Since the formula is correct so what's required is,, adjust cell references as per new data structure. Or post the Sorted data also with question. Also you may accept my post as answer since it works for you !! – Rajesh S Apr 11 '19 at 5:42

On mrexcel.com I got an answer that works no matter how the data in sheet 2 is manipulated:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.