find and search a column

[Guest]

Right I'm searching by the column letter (exp. a,b,c ect.). I want to change
my formula to search by the date that is each column. Then I want to seach
for a specific text in each cell in that column. The way I'm set up is:

A B C D E
2-oct 3-oct 4-oct 5-oct
john aa aa bb aa
jeff bb aa aa bb
jack bb bb aa bb

My formula I'm using is: =COUNTIF('sheet 1'!B:B,"*bb*")
So I get a number of 2. This formula is on sheet 2 under a date also.
What formula would I use if I wanted to search for the column 3-oct is in
then count how many bb are in that column?

 

[Domenic]

=SUMPRODUCT(ISNUMBER(SEARCH("bb",B3:E5))*(B2:E2=A8))

....where A8 contains the date of interest.

If you want the count to be case-sensitive, replace SEARCH with FIND...

=SUMPRODUCT(ISNUMBER(FIND("bb",B3:E5))*(B2:E2=A8))

Hope this helps!

 

[Guest]

I want to be able to search for the column 3-oct is in then search that
column for that specific text. I don't want to have to search by a column
letter because the date in that column could change.

 

[Domenic]

Actually, the formula will search the column whose date is equal to the
date you enter in A8.

If I understood the layout of your table correctly, I believe the first
row contains your letters, which don't come into play, the second row
contains your dates (starting at B2), and then you have your data you
want searched in B3:E5.

So if you enter a date in A8, let's say 4-oct, and search for "aa", the
formula would look in the 4-oct column and return 2.

Note that instead of hardcoding the specific text you're searching for
into the formula, you could refer to a cell which would house the
specific text of interest. I meant to do that, but I overlooked it.

 

[Dave Peterson]

I put the date in A1 of Sheet2 and put this formula in B1 of sheet2:

=COUNTIF(INDEX(Sheet1!A1:E4,,MATCH(A1,Sheet1!A1:E1,0)),"*BB*")

And it seemed to work ok.