Problem matching volume data with dates; Index/match functions tried but not working

I have now attached fixed version of the calculus as you instructed. Many thanks

I only found a "manual function" solution.. If someone has any tips it would be highly appreciated.

The function that I currently use is a manual one for each month in the "output area" using if function: IF($C$37=D$6,$D$12,0)+IF($C$37=D$6,$D$13,0)+IF($C$37=D$6,$D$14,0) + ... IF($C$37=D$6,$D$21,0).

However, I believe there could a function that can be done once and copied to all cells.. Now I have to do this row by row.

The formula from post #5 does the same thing as this shorter version:

=IF($C$37=D$6,SUM($D$12:$D$21),0)

The expected end result is in the file on rows 37-39 (inserted manually as requested by AliGW). The function used is not in the file (as manual input was requested). However, I have now attached a new version of the file that includes the function and the wanted end result in row 37. Nevertheless, this way of writing functions requires a lot of manual work (each row has to be done separately) and is complicated.

Try this:

D37 =SUMPRODUCT(($D$6:$R$6=$C37)*($D$24:$R$33=D$6)*($D$12:$R$21))

You drag the formula from post #9 to the right and down.

Had a typo, it worked perfectly. One of the smartest sum product excel functions I have seen for a while, many thanks!

Found an additional issue that I have tried to solve for couple of hours.. I have attached an example excel. In rows U46:AN80 I am trying to build a function as follows:

1) When there is a number >0 in rows U5:AN39, there are separate projects ongoing

2) If projects are ongoing, their cumulated total sales are in rows B5:39 and the amount of sales that each project generates during its life cycle is in rows D5:S40.

As an example, I have calculated manually the end result wanted in rows U46:AN51. As you can see, the project ends in rows U5:AN39 when there is a number 3. The function that I have used is IF based (e.g. "IF(U5>0,D5*$B5,0)"), but this requires again manual work. I tried with offset function, but it still did not match the start date dynamically for each project.

I don't understand what is wrong with your formula.

What is manual about it? Don't you just drag it from U46 across through AN46 and then down through AN80?

Hi,

if I drag it across, it does not match project life cycle in rows U5:AN39 with "sales roll-up" in rows D5:S40. For example, If I draw it across, AC72 gets value of 10, although it should be 0 (manually calculated it would D31 * B31 as D31 is the first month for calculating sales (0% / 100%) and B31 is projects cumulated sales.

Okay sorry for the inconvenience. My colleague figured out one solution for the problem, function goes as follows: IF(AND(U5<4,U5>0),OFFSET(U5,0,-(COLUMN(U5)-COLUMN($D5)-COUNT($T46:T46)))*$B5,"")

The problem is that the function leaves some cells blank, although there should be zero in all cells if there isn't any sales. Also, it is rather complicated formula to understand / audit.