Hi Gurus
I have a index match in the quote tab, cell D4 that will not return all results and i cant work out why.
The formula in cell D3 is exactly the same and that works, Its driving me nuts.
Please save my sanity
Thanks
Nelly
Hi Gurus
I have a index match in the quote tab, cell D4 that will not return all results and i cant work out why.
The formula in cell D3 is exactly the same and that works, Its driving me nuts.
Please save my sanity
Thanks
Nelly
Hi,
Not sure I understand. The INDEX part of the formula, which is set to generate the sum range for the SUMIFS, currently returns the range VP!F30:F37, which consists entirely of zeroes.
Regards
The Formula reads =SUMIFS(INDEX(VP!D30:F37,,MATCH(B1,VP!D29:F29)),VP!C30:C37,B14) - in my spreadsheet.
D & E both have values and f has zeros - currently the formula works on E & F but only returns zeros from D
Wierd
"currently the formula works on E & F but only returns zeros from D"
Can you explain what you mean by this? What are D, E and F here? In the sheet, the value in cell D4 is 0 - and correctly so. What should this value be? Again, what do you mean by E and F?
Regards
Sorry XOR
Im Not explaining myself very well, maybe if I tell you the result im after.
I wish the formula in C3 (data from the matrix in VP B19 - F26), to preform the same function in D4 but from the Matrix in VP B28 - F37.
Hope this helps
Nelly
Sorry - still don't understand. There is no current formula in that cell (C3), and it would be helpful if, as well as defining the intended function, you indicate what the actual result of the formula should be, and why.
Regards
Sorry again XOR, See how this is cracking me up!!! the Formula i wish to copy is in C4 in the Quote tab.
I wish it to pull the data from the Matrix B28 - F37 in the VB tab based on the cells B1 & B14 in the quote tab.
Result - Cell B1 = NF & Cell B14 = Type 2 = Result £47,97
The Formula in the quote tab C4 is based on the cells B1 & B14 and pulls the data from the vp tab cells B19 - F26
Regards
Ok, but the current formula is referencing a different range in the VP tab: D19:F26 - why is that?
And how is the table to be used - I can see at least two (Wired and Clear) - to be determined? Is this something that is to be encoded in the formula? Or do you just manually type in the appropriate range for each formula? If it's the latter, why haven't you put the correct range (B28:F37) into your formula in C4?
Regards
Im a bit lost on this Im afraid XOR
If you look in the VP tab from B2 to H13, I have the original matrix but I couldn't work out how to gain the result based on the 3 criteria that's why i split them into Clear & wired.
I need to produce the value based on the following,
NF, FD30 & FD60 are determined by cell B1
Types are determined by cell B14
Wired and Clear are determined by cell B15
All in the quote tab
Any explanation on how i can achieve this would be of great help
Thanks
Nelly
No you're talking! Why didn't you say all that in the first place?!
Try this in C4:
=SUMIFS(INDEX(VP!$D$6:$H$13,,MATCH(Quote!$B$1&Quote!$B$15,INDEX(VP!$D$3:$H$3&VP!$D$4:$H$4,,),0)),VP!$C$6:$C$13,Quote!B14)
Regards
XOR that works and more importantly i can see what i was doing wrong
Thank You for your time and putting up with my poor explanations
You're welcome - glad we got there in the end!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks