G
Guest
Hi all
I'm having trouble with the following formula:
=SUMPRODUCT(--MONTH(DU11
W11)=--MONTH(ER$11),DU14W14)*HLOOKUP(ER$11,($P$11:$DB14),ROW()-ROW($P$11))
I am trying to:
- Firstly find the rate, by matching the month in the date entered in cell ER11 to the range of months in DU11W11 and pick up the corresponding rate on row 14.
- then I want it to find the date in ER11 in the range P11B11 and go down to the current row (ie ROW()-ROW($P$11) and pick up the number of insertions entered.
- then multiply the insertions by the rate.
The formula is having trouble with the first step resulting in "0".
I'm having trouble with the following formula:
=SUMPRODUCT(--MONTH(DU11
W11)=--MONTH(ER$11),DU14W14)*HLOOKUP(ER$11,($P$11:$DB14),ROW()-ROW($P$11))I am trying to:
- Firstly find the rate, by matching the month in the date entered in cell ER11 to the range of months in DU11
W11 and pick up the corresponding rate on row 14.- then I want it to find the date in ER11 in the range P11
B11 and go down to the current row (ie ROW()-ROW($P$11) and pick up the number of insertions entered.- then multiply the insertions by the rate.
The formula is having trouble with the first step resulting in "0".