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?