• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct or choose

A

Anbuselvam K

Member
Hi

As per attached sheet i've Permanent and temporary formulations in two separate sheets.

In (3rd sheet) Master sheet A4 cell i've product name. as per the product name i've got formulation by using =SUMPRODUCT((Permanent!$A$3:$A$14='Master sheet'!$A4)*(Permanent!$B$2:$K$2='Master sheet'!B$3)*(Permanent!B3:K14))

But i need a formula that, to check the two sheets of permanent and temporary formulation sheet then the results accordingly in Master sheet B4:K4

example Product names Vitamin 1 to Vitamin 12 formulation in Permenant sheet and Vitamin 13 to Vitamin 24 formulation in Temporary sheet.

As per the Master sheet A4 cell value between Vitamin 1 to Vitamin 24, i need a results in Master sheet B4:K4

Please do the needful!

Thanks in advance.
 

Attachments

  • Formula (1).xlsx
    12.4 KB · Views: 10
Wouldn't it be just as simple to add the two together in 1 formula

MasterSheet!B4: =SUMPRODUCT((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$2:$K$2=B$3) * (Permanent!$B$3:$K$14)) + SUMPRODUCT((Temporary!$A$3:$A$14=$A4) * (Temporary!$B$2:$K$2=B$3) * (Temporary!$B$3:$K$14))

Copy across and down
 
dear Mr. Hui

please check the attachment. it's not working. Please check and modify the same.
 

Attachments

  • Formula (1).xlsx
    12.6 KB · Views: 5
Thanks to Mr. Hui and Mr. Khalid

I've confused because vitamin 8 and Vitamin 20 have a same formulation.

Anyway thanks for your valuable support.

Sincerely Yours
Anbuselvam K
 
Your confused because they have the same values in both tables
Change them and you will see they work ok
 
Dear Mr Hui

As per your suggested formula below, i got desired value. Now I have same product with different formulation by date wise.

=SUMPRODUCT((Permanent!$B$3:$B$14=$B10)*(Permanent!$C$2:$L$2=D$3) * (Permanent!$C$3:$L$14)) + SUMPRODUCT((Temporary!$B$3:$B$14=$B10) * (Temporary!$C$2:$L$2=D$3) * (Temporary!$C$3:$L$14))

So I need to extract the data by date wise. please check the attachment and suggest.

Thanks in Advance.
 

Attachments

  • Formula (1)_Hui copy.xlsx
    14.3 KB · Views: 1
Anbuselvam K said:
Dear Mr Hui

As per your suggested formula below, i got desired value. Now I have same product with different formulation by date wise.

=SUMPRODUCT((Permanent!$B$3:$B$14=$B10)*(Permanent!$C$2:$L$2=D$3) * (Permanent!$C$3:$L$14)) + SUMPRODUCT((Temporary!$B$3:$B$14=$B10) * (Temporary!$C$2:$L$2=D$3) * (Temporary!$C$3:$L$14))

So I need to extract the data by date wise. please check the attachment and suggest.

Thanks in Advance.
Click to expand...

Try,

In "Master Sheet" C4, formula copy across and down :

=IFERROR(1/(1/INDEX(Permanent!$C$3:$L$14,MATCH(1,INDEX((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$3:$B$14=$B4),0),0),MATCH(C$3,Permanent!$C$2:$L$2,0))),IFERROR(1/(1/INDEX(Temporary!$C$3:$L$14,MATCH(1,INDEX((Temporary!$A$3:$A$14=$A4)*(Temporary!$B$3:$B$14=$B4),0),0),MATCH(C$3,Temporary!$C$2:$L$2,0))),""))

Regards
Bosco
 

Attachments

  • 2WaysLookup.xlsx
    14.9 KB · Views: 5
Hi

Thanks for your reply!

As per your formulation 10-May-2017, its extract data from formulation sheet and no data in subsequence dates. it should be repeated on 11, 12, 13 and 14-May-2017 until it should get new formulation on 15-May-2017

Please do the needful.
 

Attachments

  • Formula (1)_Hui copy.xlsx
    14.5 KB · Views: 2
Anbuselvam K said:
Hi

Thanks for your reply!

As per your formulation 10-May-2017, its extract data from formulation sheet and no data in subsequence dates. it should be repeated on 11, 12, 13 and 14-May-2017 until it should get new formulation on 15-May-2017

Please do the needful.
Click to expand...

I don't understand what do you want ?

Please manually calculated/write down your expected results

Regards
 
Hi

Please see the attached copy. expected results in green colour highlighted FYI...

Main criteria are that recent date formulation should repeat for further dates until next change in formulation.
 

Attachments

  • 2WaysLookup Bosco.xlsx
    14.5 KB · Views: 4
Anbuselvam K said:
Hi

Please see the attached copy. expected results in green colour highlighted FYI...

Main criteria are that recent date formulation should repeat for further dates until next change in formulation.
Click to expand...

The new requirement need a helper column to adjust the Date Lookup Range, the revision as in :

1] Helper column, M3 enter :

=A4 and Custom Cell Format >> "Helper";;;

2] Helper column, M4 formula copy down :

=IF(ISNUMBER(MATCH(1,INDEX((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$3:$B$14=$B4)+(Temporary!$A$3:$A$14=$A4)*(Temporary!$B$3:$B$14=$B4),0),0)),A4,M3)

3] In C4, formula copy across and down :

=IFERROR(1/(1/INDEX(Permanent!$C$3:$L$14,MATCH(1,INDEX((Permanent!$A$3:$A$14=$M4)*(Permanent!$B$3:$B$14=$B4),0),0),MATCH(C$3,Permanent!$C$2:$L$2,0))),IFERROR(1/(1/INDEX(Temporary!$C$3:$L$14,MATCH(1,INDEX((Temporary!$A$3:$A$14=$M4)*(Temporary!$B$3:$B$14=$B4),0),0),MATCH(C$3,Temporary!$C$2:$L$2,0))),""))

Regards
Bosco
 

Attachments

  • 2WaysLookup(1).xlsx
    15.6 KB · Views: 6
Dear Bosco

Thanks for your valuable reply!

Can you please suggest another formula, get the same results without any helper column.

Thanks in Advance.
 
Anbuselvam K said:
Dear Bosco

Thanks for your valuable reply!

Can you please suggest another formula, get the same results without any helper column.

Thanks in Advance.
Click to expand...

The without helper column formula will become longer as in,

In C4, formula copy across and down :

=TEXT(IF(ISNUMBER(MATCH(1,INDEX((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$3:$B$14=$B4)+(Temporary!$A$3:$A$14=$A4)*(Temporary!$B$3:$B$14=$B4),0),0)),IFERROR(INDEX(Permanent!$C$3:$L$14,MATCH(1,INDEX((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$3:$B$14=$B4),0),0),MATCH(C$3,Permanent!$C$2:$L$2,0)),INDEX(Temporary!$C$3:$L$14,MATCH(1,INDEX((Temporary!$A$3:$A$14=$A4)*(Temporary!$B$3:$B$14=$B4),0),0),MATCH(C$3,Temporary!$C$2:$L$2,0))),C3),"0.00%;-0.00%;;")

Regards
Bosco
 

Attachments

  • 2WaysLookup(2).xlsx
    15.5 KB · Views: 6
Dear Bosco

Please find the attached sheet and find the results which are not showing any results due to the current date Vs. two years back date.
 

Attachments

  • 2WaysLookup Bosco.xlsx
    16.4 KB · Views: 6
How do we can change the formula to extract data with equal date and an older date and further date until it's getting new formulation?
 
Hi ,

Can you manually fill in at least for one column the expected results in the attached workbook ? Do this for the most varied or difficult column.

Narayan
 

Attachments

  • 2WaysLookup Bosco.xlsx
    14.4 KB · Views: 2
You must log in or register to reply here.
Back
Top