Count If: Using Formula to Create Range

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:

SHEET 1:

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

SHEET 2:

   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?

bhinojosa

Posted 2019-04-10T05:46:19.433

Reputation: 1

1You'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 – 2019-04-10T07:20:17.980

You can use indirect to define a data range, but you say that isn't what you need. – fixer1234 – 2019-04-10T07:20:23.193

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 – 2019-04-10T17:23:57.073

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 – 2019-04-10T17:36:28.003

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 – 2019-04-10T18:50:04.643

I realize my original post had some errors. I just fixed those. I hope that helps for anyone trying to assist me with an answer. – bhinojosa – 2019-04-10T20:59:34.750

Answers

-1

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

=COUNTIF(INDEX(Sheet2!$B:$XFD,MATCH($A2,Sheet2!$A:$A,0),),B$1)

bhinojosa

Posted 2019-04-10T05:46:19.433

Reputation: 1

This can be solves by using SUMPRODUCT function:

How it works:

Enter this Formula in Cell B2 of Sheet 1 and fill Down then Right.
```
=SUMPRODUCT((Sheet2!$A$2:$A$4=$A2)*(Sheet2!$B2:$D2=B$1))
```

Adjust Cell references in the Formula as needed.

Rajesh S

Posted 2019-04-10T05:46:19.433

Reputation: 6 800

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 – 2019-04-10T17:25:43.950

I don't see why it won't work. – Alex M – 2019-04-10T21:27:47.323

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 – 2019-04-10T22:36:42.387

@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 – 2019-04-11T05:42:14.480