Sumproduct

[Steved]

Hello from Steved

=SUMPRODUCT(--($A$1:$A$217=L$3),--($B$1:$B$217=L$1),--
($C$1:$C$217)).

The above is what it says Sumproduct, I want a similar
Formula but not do the sum.

Ok the formula looks in Col C,it finds the first occurrence
and the last occurrence and sums which it is exactly what
it is designed to do. Question keeping in mind the
sumproduct what would the formula be for it to find only
the first occurrence and return the value for that cell.
Col A = Date (Formated (d)
Col B = Date (formated TEXT(a1, "ddd")
Col C = Data
Now the tricky part
Okay This What I've tried
=SUMPRODUCT(--($A$1:$A$217=L:R),--($B$1:$B$217=L$1),--
($C$1:$C$217)). it displays #Num!
=SUMPRODUCT(--($A$1:$A$217=L$3:R7),--($B$1:$B$217=L$1),--
($C$1:$C$217)). it displays #N/A
Help

Thankyou.

 

[Leo Heuser]

Hello Steved

Here are a couple of ways to do it:

=INDEX($C$1:$C$217,MATCH(1,($A$1:$A$217=L$3)*($B$1:$B$217=L$1),0))

A bit more versatile, since it's easy to adjust to second occurrence,
third occurrence etc. is

=INDEX($C$1:$C$217,SMALL(IF(($A$1:$A$217=L$3)*($B$1:$B$217=L$1),
ROW($C$1:$C$217)-ROW($C$1)+1),M))

where M can be 1, 2, 3, 4 etc for first, second, third, fourth etc.
occurrence.

Both formulae are array formulae and must be entered with
<Shift><Ctrl><Enter>

 

[Steved]

Thankyou Leo.

-----Original Message-----
Hello Steved

Here are a couple of ways to do it:

=INDEX($C$1:$C$217,MATCH(1,($A$1:$A$217=L$3)* ($B$1:$B$217=L$1),0))

A bit more versatile, since it's easy to adjust to second occurrence,
third occurrence etc. is

=INDEX($C$1:$C$217,SMALL(IF(($A$1:$A$217=L$3)* ($B$1:$B$217=L$1),
ROW($C$1:$C$217)-ROW($C$1)+1),M))

where M can be 1, 2, 3, 4 etc for first, second, third, fourth etc.
occurrence.

Both formulae are array formulae and must be entered with
<Shift><Ctrl><Enter>

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Steved" <[email protected]> skrev i en meddelelse





.

 

[Leo Heuser]

You're welcome, Steved.

LeoH