Understood!
Niek
<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| Niek,
|
| Firstly many thanks on your help with this. The reason I ask is that
| Its difficult to simulate every scenario, this is just one sheet out of
| 32 and as this workbook will be going out to potentially 200 people on
| 200 different machines with varying amounts of data, I am just trying
| to get the optimal performance possible (Any headaches I can avoid...).
| And even though I can't see anything wrong, sometimes I read where
| people just start saying never use this method etc. I just wanted to
| make sure that this is not going to be the case... Plus if theres a
| more elegant way of doing something then I am always interested in that
| as well.
|
| Cheers,
|
| James
|
| Niek Otten wrote:
| > <are sumifs dramatically slower than vlookups?>
| >
| > If that is not evident, what does it matter?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| > | Hi,
| > |
| > | I have been experimenting, rather than using vlookups or matches I have
| > | used sumifs, the main reason for this is that if the sumif doesn't
| > | return an error it returns zero which means I then don't need to
| > | evaluate the items that I want to lookup, for example before I would
| > | have tested each of the 12 vlookups with an Iserror formula to ensure
| > | that the formula didn't error. So my question now is that is this the
| > | best approach? ie even though I am now not having to evaluate the
| > | items, are sumifs dramatically slower than vlookups?
| > |
| > | So my formula now is:
| > |
| > |
| > |
| >
=SumIf(MyItems,B4,CostOfItems)/12+SumIf(MyItems,C4,CostOfItems)/12+SumIf(MyItems,D4,CostOfItems)/12+SumIf(MyItems,E4,CostOfItems)/12+SumIf(MyItems,F4,CostOfItems)/12+SumIf(MyItems,G4,CostOfItems)/12...
| > |
| > |
| > | 12 times, so until the criteria of the sumif equals M4.
| > |
| > | Cheers,
| > |
| > | James
| > |
| > | Dave Peterson wrote:
| > |
| > | > I think I'd add a 13th column to my data that did the summing. Then return that
| > | > value.
| > | >
| > | >
(E-Mail Removed) wrote:
| > | > >
| > | > > Hi,
| > | > >
| > | > > Niek - Thanks for this, I have started to see if Match/Index is faster.
| > | > >
| > | > > Dave - Thanks, this is the route I initially took but the problem I
| > | > > have is that I want to look up 12 items so I think the array is in the
| > | > > wrong place, its almost as if I want to write the vlookup as
| > | > > Vlookup({A1;B1;C1;D1;E1;F1;G1;...},SomeRange,2,false), which I am
| > | > > starting to see isn't going to happen. - Which is a shame as this would
| > | > > be exactly what I wanted, unless I am missing something?
| > | > >
| > | > > Just to extend on the problem, I am creating a spreadsheet for salary
| > | > > costs, the persons pay scale could change at some point during the year
| > | > > so I have the person as one record and then 12 columns for their
| > | > > payscales I then need to calculate the total salary for that employee
| > | > > so I lookup each months pay scale and add them together, evaluating
| > | > > each month to make sure it hasn't been removed (ie the person is
| > | > > leaving mid way through the year).
| > | > >
| > | > > Any futher comments would be very much appreciated,
| > | > >
| > | > > Cheers,
| > | > >
| > | > > James
| > | > >
| > | > > Dave Peterson wrote:
| > | > > > How about using something like:
| > | > > >
| > | > > > =SUMPRODUCT(VLOOKUP(A1,Sheet2!A:N,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))
| > | > > >
| > | > > > If a cell is empty, then it will be treated as 0.
| > | > > >
| > | > > > Adjust the range and columns to bring back.
| > | > > >
| > | > > > I used A:N
| > | > > > and brought back the values in B:N (columns 2:13)
| > | > > >
| > | > > >
| > | > > >
| > | > > >
(E-Mail Removed) wrote:
| > | > > > >
| > | > > > > All,
| > | > > > >
| > | > > > > I want to lookup 12 columns and then add the results together, I know I
| > | > > > > can do 12 separate vlookups and add the results but is there a more
| > | > > > > efficient way of doing this? To complicate things I also need to
| > | > > > > evaluate them incase one is zero or has been left blank. I was thinking
| > | > > > > about a User Defined Function but have come unstuck in the past with
| > | > > > > items not calculating when I want them to and as I want to give this to
| > | > > > > quite a few people I want it as fool proof as possible.
| > | > > > >
| > | > > > > For example, I have the months January - December and in each month I
| > | > > > > have an item that I want to lookup and return the cost of that item to
| > | > > > > give me an annual cost.
| > | > > > >
| > | > > > > Hope this makes sense and thanks in advance,
| > | > > > >
| > | > > > > James
| > | > > >
| > | > > > --
| > | > > >
| > | > > > Dave Peterson
| > | >
| > | > --
| > | >
| > | > Dave Peterson
| > |
|
Similar Threads
