Help refining sumproduct
 

Hi guys,

In column A i have person names, each person name is repeated 18 times. In
column E have time segments 0-5, 6 -10, 11-15, right through to 86-90. These
segments are repeated for each person. For example the first 18 rows in A
will be taken up by the same person name and the first 18 rown in E will
consist of each consecutive time segment, this cycle wil then be repeated for
the next person and so forth. In column G i Have a distance, this being
distance covered by that person in that particular time segment. What I need
to return is that exact distance that corresponds to a specific person only
in a specific time segment.

At the moment I am using: sumproduct(--(A2:A224="person
name"),--(E2:E224=0-5), G2:G224).

This however is returning a summed total of all the distance values in G
matching the entered name, but I only want the single value in cell G2, thus
returing only the distance covered in 0-5 (cell E2).

Many thanks in advance,

kind regards,

Ant

Help refining sumproduct
 

Shouldn't it be

=SUMPRODUCT(--(A2:A224="person name"),--(E2:E224="0-5"), G2:G224)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Anto111" wrote in message
...
Hi guys,

In column A i have person names, each person name is repeated 18 times. In
column E have time segments 0-5, 6 -10, 11-15, right through to 86-90.
These
segments are repeated for each person. For example the first 18 rows in A
will be taken up by the same person name and the first 18 rown in E will
consist of each consecutive time segment, this cycle wil then be repeated
for
the next person and so forth. In column G i Have a distance, this being
distance covered by that person in that particular time segment. What I
need
to return is that exact distance that corresponds to a specific person
only
in a specific time segment.

At the moment I am using: sumproduct(--(A2:A224="person
name"),--(E2:E224=0-5), G2:G224).

This however is returning a summed total of all the distance values in G
matching the entered name, but I only want the single value in cell G2,
thus
returing only the distance covered in 0-5 (cell E2).

Many thanks in advance,

kind regards,

Ant

Help refining sumproduct
 

Fantastic. Done.

Very basic oversight.

Cheers Bob

"Bob Phillips" wrote:

Shouldn't it be

=SUMPRODUCT(--(A2:A224="person name"),--(E2:E224="0-5"), G2:G224)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Anto111" wrote in message
...
Hi guys,

In column A i have person names, each person name is repeated 18 times. In
column E have time segments 0-5, 6 -10, 11-15, right through to 86-90.
These
segments are repeated for each person. For example the first 18 rows in A
will be taken up by the same person name and the first 18 rown in E will
consist of each consecutive time segment, this cycle wil then be repeated
for
the next person and so forth. In column G i Have a distance, this being
distance covered by that person in that particular time segment. What I
need
to return is that exact distance that corresponds to a specific person
only
in a specific time segment.

At the moment I am using: sumproduct(--(A2:A224="person
name"),--(E2:E224=0-5), G2:G224).

This however is returning a summed total of all the distance values in G
matching the entered name, but I only want the single value in cell G2,
thus
returing only the distance covered in 0-5 (cell E2).

Many thanks in advance,

kind regards,

Ant