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

wrote in message ups.com...
| 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.
|
| 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,fal se), 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)
|
|
|
| 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
|