Sumproduct formula..

[sam1105]

Hi All,

Needed a formula from which i can come to know artist name..working on which sequence nos as per Animation Take wise.
For e.g - seq 101 sam is working on Animation Take 1 & 101 Hussain is working on Animation take 2 on the same seq

The takes can be more than attached example sheet..

please find the attached sheet for your reference...

[Glenn Kennedy]

I think you need to look at you sheet again!! Sam doesn't seem to appear anywhere and Hussain only appears against take 1. This is totally confusing.

[sam1105]

Hii..

My mistake...pls find the new attachment...

[ThirdFret]

It might be more clear if you uploaded an example of your desired output. I'm not sure which cells you're trying to calculate or what your desired result would be.

[sam1105]

Hello Guys,

Sorry for delay reply...find the attachment with the example..

[mangesh.mehendale]

Can you highlight desired result in yellow??...

[sam1105]

HI

I have highlighted the result which i require..seq nos will be entered by me manually everytime but the names of artist should be populated automatically..

[mangesh.mehendale]

Please Login or Register  to view this content.

[Glenn Kennedy]

This STILL does not make sense. Why is the answer to 100 Take 1 Sam, when (yellow shaded cells) when Sam, Ravi, Hussain, Chawda, Pandya & Kumar all worked on it too?
Why is the answer to 103 Chawda and not Jay for 103 Take 1 (pink cells)?

For scene 100 take 2, you have multiple answers (Hussain and Padeep). Why do you want multiple answers for ascene 100 take 2 and a single answer for scene 100 Take 1?


please take some time to check your questions AND your expected answers!!!!

[sam1105]

Hii Glen..

You are rite..i actually need multiple answers as u mentioned for all the takes...
For eg. seq 100 all the names under that should be populated, then like that only 102,103,105..etc..

[mangesh.mehendale]

If you required multiple answers then easy solution(I am not saying this is the only solution) is with Vlookup you make summary sheet... see in attached file..

[sam1105]

Thanks mangesh..is it possible that the names should be reflected against status..i mean i need only that names whose status are Client retake or YTS and rest all the status against names formula should ignore
For e.g. 100 name should be sam and kumar and no one else,,becoz their status is Client retake & is it possible that the same names can be reflected only 1 time...thank you ..

[mangesh.mehendale]

to ignoring duplicate names is possible with Macros...
right now result as per status is possible.. See attached sheet

[sam1105]

Hi Mangesh,

Thank you for ur time...i can understand it is only possible with macros,..its fine if it is not done too...your formula is grt.i need one change in that..if the status is Client Retake then artist name should be reflected under the seq nos & if it is not Client retake then the name should not be populated & 2nd thing is i will be having Client Retake and YTS both status at the same time in same takes..please find the attached sheet in which i highlighted what will be required as per the takes under the seq no

[mangesh.mehendale]

hii see attached sheet hope this will work...
Please check Range for both take 1 and 2 formulae while putting formulae to original sheet

[Glenn Kennedy]

Hi there. Try this instead. Again it is an array formula. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

=IFERROR(INDEX($J$3:$J$18, SMALL(IF((E$23=$A$3:$A$18)*(COUNTIF(E$23:E23, $J$3:$J$18)=0), ROW($A$3:$A$18)-MIN(ROW($A$3:$A$18))+1, ""), 1)),"")

Don't type the curly braces yourself - it won't work...


It gives a unique list as well !!

[sam1105]

HI Glen,,...

Thanks it worked perfectly...thank u again both Mangesh & glenn...

[mangesh.mehendale]

mark thread as solved.. see at top "tread tools"..

change title somthing like... "Unique list as per criteria"

[Glenn Kennedy]

You're welcome and thanks for the Reputation.