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?