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

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.

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

--

HTH

RP

"jtinne" wrote in message
...
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.

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.

"Bob Phillips" wrote:

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

--

HTH

RP

"jtinne" wrote in message
...
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.

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!

In article ,
jtinne wrote:

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.

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

--
Regards
Frank Kabel
Frankfurt, Germany


jtinne wrote:
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.