1. ## Sumproduct (I think)

Row 1, columns A-CC in worksheet 2 are various names. Row 4, columns A-CC in worksheet are one-word descriptions of the names. Row 473, columns A-CC contain formulas that return the dates of interviews with the persons named in row 1, from data in another section of worksheet 2. The formula returns a blank (i. e., "") if no interview was done.

What I would like to do is where a name that appears in row 1 on worksheet 2 is listed in column A on worksheet, and a certain description is present in row 4, say, a "P", look to worksheet 2 and insert in column B on worksheet 1 the date, if any, of the interview of that person that is in row 473 on worksheet 2, but only when the description is say a "P".

Any ideas?

2. How about something like the array (confirm with ctrl-shift-enter):
=INDEX(Worksheet2!A473:CC473,MIN(IF((Worksheet2!A1 :CC1="Tom")*(Worksheet2!A4:CC4="P"),COLUMN(Workshe et2!A473:CC473))))

It will find the value in the first column of row 473 for "Tom" in row1 with "P" in row 4. You could also find the average date, min, max, etc if there are more than 1 date meeting the criteria...

[SumProduct will not work since your formula may be null and thus will lead to #Value errors when used in multiplication. If you replaced the nulls with zeroes and knew there was only 1 value in row 473 meeting the criteria (or 1 date and the rest zero), then sumproduct could work:
=SUMPRODUCT((Worksheet2!A1:CC1="Tom")*(Worksheet2! A4:CC4="P")*Worksheet2!A473:CC473)

If using XL2007 or greater you can use SUMIFS even if 473 has null strings (so is better than sumproduct in that regard):
=SUMIFS(Worksheet2!A473:CC473,Worksheet2!A1:CC1,"T om",Worksheet2!A4:CC4,"P")

But if there are more than 1 matching dates the dates will all be added in sumifs and sumproduct so may not be what you want exactly...]

Steve
PS. This doesn't really seem that much different than your question at https://windowssecrets.com/forums/sho...roduct-I-think

Steve

3. Thanks Steve, I'll give it a try. You are correct, the two questions are similar, but I thought as opposed to summing values I was looking for a particular date, this was a different request.

4. FYI SUMPRODUCT should work if you use:
=SUMPRODUCT((Worksheet2!A1:CC1="Tom")*(Worksheet2! A4:CC4="P"),Worksheet2!A473:CC473)

Alternatively:
=LOOKUP(2,1/((Worksheet2!A1:CC1="Tom")*(Worksheet2!A4:CC4="P") ),Worksheet2!A473:CC473)
will return the last item in row 473 that matches both criteria. No array-entry required.

5. ## The Following User Says Thank You to rory For This Useful Post:

sdckapr (2011-05-27)

6. FYI SUMPRODUCT should work if you use:
=SUMPRODUCT((Worksheet2!A1:CC1="Tom")*(Worksheet2! A4:CC4="P"),Worksheet2!A473:CC473)
Thanks, Rory.

I hadn't realized the subtle difference between SUMPRODUCT(A*B*C) and SUMPRODUCT(A*B,C) when text was involved...

Steve

7. There are situations where you can't use that and have to use the multiplication syntax all the way (eg multicolumn values range) but as a general rule I prefer the comma syntax for safety.

8. Thanks Rory

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•