Sumif or Better Alternative?

[JeffTO]

Hi All

I know how to use the conditional sum or sumif function - I am trying
to see if there is a better way to do somethign that works wth sumif -
but there really is no "summing" going on - and since I use a lot of
these in a spreadhsheet and it takes more time than I would like to
process I am looking for a potential alternative.

Anyone have any other ideas that might be more efficient than using a
SumIf if there is no "summing" actually going on - it is sort of like
a lookup inside a lookup but since I done have the middle range needed
for the 2nd lookup I am not sure what to do

Thanks in advance for any assistance

Jeff

Here is what I have

Value Formua Result
A SUM(IF(IDList=ID,IF(Code=A2,Count,0))) 100
B SUM(IF(IDList=ID,IF(Code=A3,Count,0))) 0
C SUM(IF(IDList=ID,IF(Code=A4,Count,0))) 100
D SUM(IF(IDList=ID,IF(Code=A5,Count,0))) 100
E SUM(IF(IDList=ID,IF(Code=A6,Count,0))) 0
F SUM(IF(IDList=ID,IF(Code=A7,Count,0))) 100

ID = 22222

IDList Code Count
11111 A 100
11111 B 100
11111 C 100
11111 D 100
11111 E 100
11111 F 100
22222 A 100
22222 C 100
22222 D 100
22222 F 100
33333 B 100
33333 D 100
33333 E 100
44444 A 100
44444 B 100
44444 C 100
44444 D 100
44444 E 100
44444 F 100
55555 A 100
55555 B 100
55555 C 100
55555 D 100
55555 E 100
55555 F 100

 

[Pete_UK]

You could insert a new column between Code and Count in your ID Table,
and then put a formula like this:

=A2&B2

to join those two fields together to form a unique identifier (copy
down as required). Then you could have this type of formula in your
summary table:

=VLOOKUP(ID&A2,table,2,0)

where table is a named range covering columns C and D of the ID Table.
Copy the formula down as required.

Hope this helps.

Pete

 

[JeffTO]

Always somethign so simple

Thanks Pete - much more efficient than trying to use a SumIf when
there is nothing to actually sum

Jeff