If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Threed Function
Column B has a place name in e.g. Exeter on one worksheet or Barnsley in
another. E129 is being the condition Column C has a series of numbers 1- 27. F129 and D129 are the conditions e.g. 22 and 5 So what I am trying to achieve is if Column B contains Exeter then look at that specific worksheet withing the range of worksheets and sumproduct two lines of cash flows based on the two rows where the numbers 22 and 5 are in Column C Hope you can understand that "Domenic" wrote: =SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$1 54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$ H$154:$H$180)) In the above formula, the second and third arguments of SUMPRODUCT both refer to Column C, for which two different criteria has to met. Can you confirm which columns need to be referenced, and the condition that applies to each? -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , MattEd101 wrote: I say works a treat my new formula turned out to be =SUMPRODUCT(--(THREED(start:End!$A$9:$A$13)=$B9),--(THREED(start:End!$B$9:$B$1 3)=$A9),THREED(start:End!D$9$13)) Which worked. This was only a test however so I tried to replicate with the following (which has 3 conditions) =SUMPRODUCT(--(THREED(Start:End!$B$154:$B$180)=$E129),--(THREED(Start:End!$C$1 54:$C$180)=$D129),--(THREED(Start:End!$C$154:$C$180)=$F129),THREED(Sta rt:End!$ H$154:$H$180)) But each time I press F9 on the Threed arrays, the result does not refer to the data in the colums specified. E.g C154:C180 should look at an array of numbers, but when you press F9, it actually returns an array of place names which is what B154:B180 should be looking at! I thought anchoring the array would help but apparently not "MattEd101" wrote: I realised I hadn't used ranges of the same size this morning, works a treat now Cheers for the response You will probably see me on here again at some point. Matt "Domenic" wrote: The ranges for each argument need to be the same size. For which range does the first condition need to be met? -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , MattEd101 wrote: Dominic I am having reall issues with Threed. Im trying to incorporate it into a sumproduct as shown in your last response. The formula is =SUMPRODUCT(--(THREED(Start:End!A3)=A6),--(THREED(Start:End!A6:A9)=$B6), THREED (Start:End!C69)) However the first two Threed arrays look at text rather than figures. For example Start:end!A3 looks at two worksheets with the words Exeter in worksheet 1 and Barnsley in worksheet 2. However within the formula above the threed function returns {0;"Exe ";"Barn ";0} which when I put in the condition =A6 of "Exeter", the formula returns False. Is this a limitation of Threed or am I doing something wrong? Cheers Matt |
Thread Tools | |
Display Modes | |
|
|