Sumif or Better Alternative?
 

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

Sumif or Better Alternative?
 

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

On Jul 24, 4:15*pm, JeffTO wrote:
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

Sumif or Better Alternative?
 

Always somethign so simple

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

Jeff