find a date on sheet 2 and count text in that column

[Guest]

I'm lookung for the formula to use that if I was under one date on one page,
to look what date im under go to the 2nd page, find that date then count the
text specified under that date. My pages are set up as follows:

sheet 1

A B C D
E
1| 1-oct 2-oct 3-oct
4-oct
2| john text 1 text 1 text 2
text 1
3| jack text 1 text 2 text 1
text 1
4| jeff text 2 text 2 text 1
text 2


sheet 2

A B C D
E
1| 1-oct 2-oct 3-oct
4-oct
2| text 1 2 1 2
2
3| text 2 1 2 1
1


For instance, what formula would I use if I wanted to search for "1-oct" on
sheet 1 for "text 1" and have the total in cell under "1-oct" on sheet 2 in
line with "text 1" row? Keep in mind my dates will change so I want to be
able to reference to the text that is in the cell above where my total will
be.

 

[Bob Phillips]

=SUMPRODUCT((Sheet2!$B$1:$E$1=B$1)*(Sheet2!$A$2:$A$10=B2),Sheet2!$B$2:$E$10)

 

[Guest]

I want exel to find what column that date is in on sheet 1, then search that
column for that specified text, then count how many times that text appears.

 

[Domenic]

Enter the following formula in B2 of Sheet2 and copy across and down:

=SUMPRODUCT((Sheet1!$B$2:$E$4=Sheet2!$A2)*(Sheet1!$B$1:$E$1=Sheet2!B$1))

Adjust the range accordingly.

Hope this helps!

 

[Frank Kabel]

Hi
in B2 of your second sheet enter the formula:
=COUNTIF(OFFSET('sheet1'!$A$1:$A$100,0,MATCH(B$1,'sheet1'!$A$1:$E$1,0))
,$A2)
and copy this formula across