Formula for populating cell/s based on criteria

T

Tizzi203040

New Member
Joined
Jul 1, 2015
Messages
17
Hi,

I am trying to do a function in excel but i am having difficulty coming up with the formula for it. So basically what i want to do is:

Column A(Cells in this column are drop downs) Column B Column C Column D Column E
Product A $10,000
Product B $20,000
Product C $30,000

I want to be able to check a box or insert a Y(Yes) in Columns C, D or E and have it populate with the price from column B. Now, Vlookup will not work because I want to be able to select which columns should be populated with that price. I have 10+ columns so instead of typing the price over and over again I figured if there was a way to do it with a formula. Is there a way to combine formulas so that if I select Y from a drop down then that same cell will populate with the price?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Re: Need formula for populating cell/s based on criteria

Try with the following code

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C:L")) Is Nothing Then
        Target.Value = Cells(Target.Row, "B").Value
        Cancel = True
    End If
End Sub

Put the code in the events of your sheet

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

-----
Now, double click on the cell where you want the price
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

Thank you this worked!

Now, unfortunately the users of the excel sheet are hesitant to use macros due to security reasons so i'm wondering if you can help me out again.

What i want to do as an alternative is to create a Yes/No drop down for each cell in Column C;D & E (in worksheet in have a total of 12 columns which represent 12 months). In another table i want to take the total price from column B, check to see how many cells contain the checkmark YES and multiply it so it will give me the total price.

Example:

Total Unit Price/Monthly is $100
Contract is for 5 months on a 12 month max term so 5 months out of the 12 are check off as YES. I want to check all columns for that product, identify how many columns are check with YES and multiply it by the monthly unit price.

I can't have macros so I was wondering if there is a way to do this with a formula? Thank you again!
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

Tizzi203040 said:
Thank you this worked!

Now, unfortunately the users of the excel sheet are hesitant to use macros due to security reasons so i'm wondering if you can help me out again.

What i want to do as an alternative is to create a Yes/No drop down for each cell in Column C;D & E (in worksheet in have a total of 12 columns which represent 12 months). In another table i want to take the total price from column B, check to see how many cells contain the checkmark YES and multiply it so it will give me the total price.

Example:

Total Unit Price/Monthly is $100
Contract is for 5 months on a 12 month max term so 5 months out of the 12 are check off as YES. I want to check all columns for that product, identify how many columns are check with YES and multiply it by the monthly unit price.

I can't have macros so I was wondering if there is a way to do this with a formula? Thank you again!
Click to expand...


Something like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:37.07px;" /><col style="width:98.85px;" /><col style="width:26.61px;" /><col style="width:28.51px;" /><col style="width:29.47px;" /><col style="width:27.56px;" /><col style="width:31.37px;" /><col style="width:26.61px;" /><col style="width:22.81px;" /><col style="width:29.47px;" /><col style="width:28.51px;" /><col style="width:26.61px;" /><col style="width:30.42px;" /><col style="width:28.51px;" /><col style="width:57.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#ffff00; ">Price/Monthly</td><td style="background-color:#ffff00; ">Jan</td><td style="background-color:#ffff00; ">Feb</td><td style="background-color:#ffff00; ">Mar</td><td style="background-color:#ffff00; ">Apr</td><td style="background-color:#ffff00; ">May</td><td style="background-color:#ffff00; ">Jun</td><td style="background-color:#ffff00; ">Jul</td><td style="background-color:#ffff00; ">Aug</td><td style="background-color:#ffff00; ">Sep</td><td style="background-color:#ffff00; ">Oct</td><td style="background-color:#ffff00; ">Nov</td><td style="background-color:#ffff00; ">Dec</td><td style="background-color:#ffff00; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">100</td><td >yes</td><td >yes</td><td >yes</td><td >yes</td><td >yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">500</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >O2</td><td >=COUNTIF(C2:N2,"yes")*B2</td></tr></table></td></tr></table>
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

Thank you once again!

What if i have a fixed fee and lets say the default for that fixed fee is 12 month term (e.g., $100,000 for 12 months). Now, I have a working grid (where the user is checking "yes" for the month they want) and another grid where the user is going to copy nd paste it in a document. I want....to default the 100k to a 12 month term but give the option the user to change it if they want a shorter term, but if they do decide on a shorter term I want a formula to redistribute the 100k to that shorter term.

Example:
Jan/Feb/Mar/Apr/Jun/Jul/Aug/Sep/Oct/Nov/Dec - Spread out total price across 12 months evenly
User goes in and deletes "yes" from January & February leaving only a 10 month term.
I want a formula to recognize that "Yes" is deleted from the two cells and to now redistribute the amount across 10 months.

Hopefully this makes sense....
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

Tizzi203040 said:
I want a formula to recognize that "Yes" is deleted from the two cells and to now redistribute the amount across 10 months.
Click to expand...

In which cells do you want to distribute the amount?
You can put an example with real data and real cells.
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

Burn Plan (Months)

Code
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]101[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Cell A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 17[/TD]
[TD]Training[/TD]
[TD]101[/TD]
[TD]20000[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]

In cell D17 - O17, I want to look up the code, find it in the burn plan above and populate the months with a "Y" based on the above burn plan. I want this to be interactive, because since the field is a drop down they will select any code, so I want the months to be filled with Y's based on the code and the burn plan. (so if the user selects code 105, then the cells will change and only 5 months will be populated with "Y")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]101[/TD]
[TD]20000[/TD]
[TD]$1667[/TD]
[TD]
$1667​
<strike></strike>
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
<strike></strike>
[/TD]
[TD]
$1667​
[/TD]
[TD]
$1667​
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike><strike></strike>
In this table I want to count the number of "Y" from Table 1 above, look up the price for that code and distribute the amount based on the number of "Y" to each months (e.g, Code 101 has a burn plan of 12 months. Price is 20,000. Take 20,000 divide it by 12, and populate each month by that amount. )
What i also want it to do is that if the user let's say decides to create his own burn plan then i want the $ amounts in the months above to auto redistribute (e.g, default for code 101 is 12 months. Let's say user wants to do the contarct fo 10 months, so they delete 2 months from table 1. In table two i want it to auto calcualte the monthly amounts by 10 months now.


Note: I have part of the calcualtion for table where it's looking up the Y's, counting and then diving them. What I am missing is making Table 1 interactive where it would auto-populate with "Y" based on which code they select and defaulting it from the burn plan.
=IF($E$24:$P$24="Y",$D$24/COUNTIF($E$24:$P$24,"Y")," ")<strike>
</strike><strike></strike>
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

I am a bit lost.
Are you going to have 3 tables?
Table 1 captures Y.
In table 2 a line is filled based on the Y of table 1
In table 3 the amount is distributed according to the Y of table 2 ???
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

You are correct. sorry, i might be making it a little more confusing than it is but it's difficult to explain it.
 
Upvote 0
Re: Need formula for populating cell/s based on criteria

Tizzi203040 said:
You are correct. sorry, i might be making it a little more confusing than it is but it's difficult to explain it.
Click to expand...


Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64.63px;" /><col style="width:37.07px;" /><col style="width:39.92px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >TABLE 1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">CODE</td><td style="background-color:#ffff00; ">Ene</td><td style="background-color:#ffff00; ">Feb</td><td style="background-color:#ffff00; ">Mar</td><td style="background-color:#ffff00; ">Abr</td><td style="background-color:#ffff00; ">May</td><td style="background-color:#ffff00; ">Jun</td><td style="background-color:#ffff00; ">Jul</td><td style="background-color:#ffff00; ">Ago</td><td style="background-color:#ffff00; ">Sep</td><td style="background-color:#ffff00; ">Oct</td><td style="background-color:#ffff00; ">Nov</td><td style="background-color:#ffff00; ">Dic</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">101</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">102</td><td >y</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">103</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">104</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">105</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >TABLE 2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; ">Name</td><td style="background-color:#92d050; ">Code</td><td style="background-color:#92d050; ">Price</td><td style="background-color:#92d050; ">Ene</td><td style="background-color:#92d050; ">Feb</td><td style="background-color:#92d050; ">Mar</td><td style="background-color:#92d050; ">Abr</td><td style="background-color:#92d050; ">May</td><td style="background-color:#92d050; ">Jun</td><td style="background-color:#92d050; ">Jul</td><td style="background-color:#92d050; ">Ago</td><td style="background-color:#92d050; ">Sep</td><td style="background-color:#92d050; ">Oct</td><td style="background-color:#92d050; ">Nov</td><td style="background-color:#92d050; ">Dic</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Training</td><td style="text-align:right; ">101</td><td style="text-align:right; ">20000</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >WW</td><td style="text-align:right; ">105</td><td style="text-align:right; ">15000</td><td >y</td><td >y</td><td >y</td><td >y</td><td >y</td><td style="text-align:right; "> </td><td style="text-align:right; "> </td><td style="text-align:right; "> </td><td style="text-align:right; "> </td><td style="text-align:right; "> </td><td style="text-align:right; "> </td><td style="text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >TABLE 3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffc000; ">Name</td><td style="background-color:#ffc000; ">Code</td><td style="background-color:#ffc000; ">Price</td><td style="background-color:#ffc000; ">Ene</td><td style="background-color:#ffc000; ">Feb</td><td style="background-color:#ffc000; ">Mar</td><td style="background-color:#ffc000; ">Abr</td><td style="background-color:#ffc000; ">May</td><td style="background-color:#ffc000; ">Jun</td><td style="background-color:#ffc000; ">Jul</td><td style="background-color:#ffc000; ">Ago</td><td style="background-color:#ffc000; ">Sep</td><td style="background-color:#ffc000; ">Oct</td><td style="background-color:#ffc000; ">Nov</td><td style="background-color:#ffc000; ">Dic</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Training</td><td style="text-align:right; ">101</td><td style="text-align:right; ">20000</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td><td style="text-align:right; ">1667</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >WW</td><td style="text-align:right; ">105</td><td style="text-align:right; ">15000</td><td style="text-align:right; ">3000</td><td style="text-align:right; ">3000</td><td style="text-align:right; ">3000</td><td style="text-align:right; ">3000</td><td style="text-align:right; ">3000</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D11</td><td >=VLOOKUP($B11,$A$3:$M$7,MATCH(D$10,$A$2:$M$2,0),0)</td></tr><tr><td >D16</td><td >=IF(VLOOKUP($B16,$B$11:$O$12,MATCH(D$15,$B$10:$O$10,0),0)="y",$C16/ SUMPRODUCT(($A$11:$A$12=$A16)*($D$11:$O$12="y")),"")</td></tr></table></td></tr></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
N
  • Question Question
Column with Min/Average values at the end of Pivot Table
Replies
0
Views
170
Nurces
N
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S
L
  • Solved
YoY Increase over n Years - Sum all Years in a single formula
Replies
6
Views
68
Cubist
Cubist
E
  • Solved
How to find the next 0 in a range, after a specific date. What's the best way to do that?
Replies
6
Views
107
rowebca
rowebca

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top