Double Vertical Index & Match
 

Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be duplicated to
i need to validate by date and time which were created seperately by another
system. i'd like to do this without creating an extra or hidden columns.

I would like a formula to return the scrore 95 from sheet 1 that corresponds
to

Double Vertical Index & Match
 

If there will only be one match, or if you'll want to sum the C values if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be duplicated
to
i need to validate by date and time which were created seperately by
another
system. i'd like to do this without creating an extra or hidden columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to

Double Vertical Index & Match
 

I'm using the following and I am getting a return of 0. is there something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

"PCLIVE" wrote:

If there will only be one match, or if you'll want to sum the C values if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be duplicated
to
i need to validate by date and time which were created seperately by
another
system. i'd like to do this without creating an extra or hidden columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to

Double Vertical Index & Match
 

It may have something to do with what sheet you are placing this formula.
Try adding the Sheet2 refererence.

=SUMPRODUCT(--(data!A4:A12=Sheet2!B12),--(data!B4:B12=Sheet2!A14),data!C4:C12)

Also, you may need to verify that the format of your data in column A
matches the format of the criteria in B12. Same thing goes with column B
and A14.

Regards,
Paul



--

"Qaspec" wrote in message
...
I'm using the following and I am getting a return of 0. is there something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

"PCLIVE" wrote:

If there will only be one match, or if you'll want to sum the C values if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be
duplicated
to
i need to validate by date and time which were created seperately by
another
system. i'd like to do this without creating an extra or hidden
columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to

Double Vertical Index & Match
 

Still showing 0,

=SUMPRODUCT(--(data!A4:A12=CRC!B12),--(data!B4:B12=CRC!A14),data!C4:C12)


"PCLIVE" wrote:

It may have something to do with what sheet you are placing this formula.
Try adding the Sheet2 refererence.

=SUMPRODUCT(--(data!A4:A12=Sheet2!B12),--(data!B4:B12=Sheet2!A14),data!C4:C12)

Also, you may need to verify that the format of your data in column A
matches the format of the criteria in B12. Same thing goes with column B
and A14.

Regards,
Paul



--

"Qaspec" wrote in message
...
I'm using the following and I am getting a return of 0. is there something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

"PCLIVE" wrote:

If there will only be one match, or if you'll want to sum the C values if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be
duplicated
to
i need to validate by date and time which were created seperately by
another
system. i'd like to do this without creating an extra or hidden
columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to

Double Vertical Index & Match
 

Trying Copying cell B12 which contains the date criteria and paste it in A6
of your data (which should be the same). This is to test if your criteria
is actually matching your data. Press F9 to recalculate and see if your
formula shows the correct figure. If not, then copy cell A14 which appears
to have the text "0000" in it, and paste it in cell B6 of your data.

Note: I'm assuming A6 and B6 is where this falls within your sample data
since your ranges start at row 4.

As mentioned before, I'm still thinking that your criteria is actually
matching to the data in your range, even though it may appear to be the
same.

HTH,
Paul


--

"Qaspec" wrote in message
...
Still showing 0,

=SUMPRODUCT(--(data!A4:A12=CRC!B12),--(data!B4:B12=CRC!A14),data!C4:C12)


"PCLIVE" wrote:

It may have something to do with what sheet you are placing this formula.
Try adding the Sheet2 refererence.

=SUMPRODUCT(--(data!A4:A12=Sheet2!B12),--(data!B4:B12=Sheet2!A14),data!C4:C12)

Also, you may need to verify that the format of your data in column A
matches the format of the criteria in B12. Same thing goes with column B
and A14.

Regards,
Paul



--

"Qaspec" wrote in message
...
I'm using the following and I am getting a return of 0. is there
something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

"PCLIVE" wrote:

If there will only be one match, or if you'll want to sum the C values
if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be
duplicated
to
i need to validate by date and time which were created seperately by
another
system. i'd like to do this without creating an extra or hidden
columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to

Double Vertical Index & Match
 

Actually the data was being stored as text in column c, once i changed it to
number it worked. Thank you for your help PCLIVE.

"PCLIVE" wrote:

Trying Copying cell B12 which contains the date criteria and paste it in A6
of your data (which should be the same). This is to test if your criteria
is actually matching your data. Press F9 to recalculate and see if your
formula shows the correct figure. If not, then copy cell A14 which appears
to have the text "0000" in it, and paste it in cell B6 of your data.

Note: I'm assuming A6 and B6 is where this falls within your sample data
since your ranges start at row 4.

As mentioned before, I'm still thinking that your criteria is actually
matching to the data in your range, even though it may appear to be the
same.

HTH,
Paul


--

"Qaspec" wrote in message
...
Still showing 0,

=SUMPRODUCT(--(data!A4:A12=CRC!B12),--(data!B4:B12=CRC!A14),data!C4:C12)


"PCLIVE" wrote:

It may have something to do with what sheet you are placing this formula.
Try adding the Sheet2 refererence.

=SUMPRODUCT(--(data!A4:A12=Sheet2!B12),--(data!B4:B12=Sheet2!A14),data!C4:C12)

Also, you may need to verify that the format of your data in column A
matches the format of the criteria in B12. Same thing goes with column B
and A14.

Regards,
Paul



--

"Qaspec" wrote in message
...
I'm using the following and I am getting a return of 0. is there
something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

"PCLIVE" wrote:

If there will only be one match, or if you'll want to sum the C values
if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be
duplicated
to
i need to validate by date and time which were created seperately by
another
system. i'd like to do this without creating an extra or hidden
columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to

Double Vertical Index & Match
 

Stupid me. I should have thought to mention that. Anyway, I'm glad it's
working for you now.

Regards,
Paul

--

"Qaspec" wrote in message
...
Actually the data was being stored as text in column c, once i changed it
to
number it worked. Thank you for your help PCLIVE.

"PCLIVE" wrote:

Trying Copying cell B12 which contains the date criteria and paste it in
A6
of your data (which should be the same). This is to test if your
criteria
is actually matching your data. Press F9 to recalculate and see if your
formula shows the correct figure. If not, then copy cell A14 which
appears
to have the text "0000" in it, and paste it in cell B6 of your data.

Note: I'm assuming A6 and B6 is where this falls within your sample data
since your ranges start at row 4.

As mentioned before, I'm still thinking that your criteria is actually
matching to the data in your range, even though it may appear to be the
same.

HTH,
Paul


--

"Qaspec" wrote in message
...
Still showing 0,

=SUMPRODUCT(--(data!A4:A12=CRC!B12),--(data!B4:B12=CRC!A14),data!C4:C12)


"PCLIVE" wrote:

It may have something to do with what sheet you are placing this
formula.
Try adding the Sheet2 refererence.

=SUMPRODUCT(--(data!A4:A12=Sheet2!B12),--(data!B4:B12=Sheet2!A14),data!C4:C12)

Also, you may need to verify that the format of your data in column A
matches the format of the criteria in B12. Same thing goes with
column B
and A14.

Regards,
Paul



--

"Qaspec" wrote in message
...
I'm using the following and I am getting a return of 0. is there
something
wrong with the formula the way it is entered?

=SUMPRODUCT(--(data!A4:A12=B12),--(data!B4:B12=A14),data!C4:C12)

"PCLIVE" wrote:

If there will only be one match, or if you'll want to sum the C
values
if
there are multiple matches, then you could use SUMPRODUCT.

=SUMPRODUCT(--(A2:A5=Sheet2!A15),--(B2:B5=Sheet2!A14),C2:C5)

HTH,
Paul

--

"Qaspec" wrote in message
...
Sheet1
A B C
1 Date Time Score
2 3/1/08 0000 91
3 3/1/08 0600 96
4 3/2/08 0000 95
5 3/2/08 0600 97

Sheet2
A14 = 0000
B12 = 3/2/08

Times and scores can be duplicated and date and scores can be
duplicated
to
i need to validate by date and time which were created seperately
by
another
system. i'd like to do this without creating an extra or hidden
columns.

I would like a formula to return the scrore 95 from sheet 1 that
corresponds
to