Sumproduct on multiple "or" conditions

[watchtower]

I have the below date from which I want to sum the "amount" column based on
the "ID" column. (There are other criteria that I have to use as well, but
they will be in other columns.) The sumproduct function works very well if I
only want to sum the amounts of one ID, but I am not sure how to sum the
amounts of multiple IDs using one formula. If I want to sum the amounts of
both 1340 and 5450, I am currently using:

=sumproudct(--(ID=1340),amt)+sumproduct(--(ID=5450,amt)
=(118)+(98)
=216

This works fine if I am only using a couple criteria, but my formulas are
getting ridiculously long (on the edge of being too long for Excel) and are
becoming difficult to adjust.

Any thoughts?

Thanks,
Adam

ID Amount
1340 9
1340 5
1340 28
1340 15
1340 61
5450 10
5450 12
5450 12
5450 12
5452 52
5450 52
5452 12
5452 63

1340 118
5450 98
5452 127
Total 343

 

[aidan.heritage]

I have the below date from which I want to sum the "amount" column based on
the "ID" column. (There are other criteria that I have to use as well, but
they will be in other columns.) The sumproduct function works very well if I
only want to sum the amounts of one ID, but I am not sure how to sum the
amounts of multiple IDs using one formula. If I want to sum the amounts of
both 1340 and 5450, I am currently using:

=sumproudct(--(ID=1340),amt)+sumproduct(--(ID=5450,amt)
=(118)+(98)
=216

This works fine if I am only using a couple criteria, but my formulas are
getting ridiculously long (on the edge of being too long for Excel) and are
becoming difficult to adjust.

Any thoughts?

Thanks,
Adam

ID Amount
1340 9
1340 5
1340 28
1340 15
1340 61
5450 10
5450 12
5450 12
5450 12
5452 52
5450 52
5452 12
5452 63

1340 118
5450 98
5452 127
Total 343

Why not create another column which would use a lookup to determine
wether or not to include the value - the sumproduct would then just
use this column to determine if the condition is true or not

 

[watchtower]

Adding another column would work fine, but I am importing data and I would
rather not have to format it at all. All of this data will be on a separate
sheet for input only. My goal is to import the data and have the cells
update automatically.

 

[aidan.heritage]

Adding another column would work fine, but I am importing data and I would
rather not have to format it at all. All of this data will be on a separate
sheet for input only. My goal is to import the data and have the cells
update automatically.






- Show quoted text -

Ok, but how do we know the criteria to sum - do these criteria change
over time, or are they locked in - if so, probably a COUNTIF to count
the individual ID against a list of SUMMING ID's would do it - that
would return a 1 or 0, which would work well with the SUMPRODUCT
function

 

[Rick Rothstein \(MVP - VB\)]

I have the below date from which I want to sum the "amount" column based on

the "ID" column. (There are other criteria that I have to use as well,
but
they will be in other columns.) The sumproduct function works very well
if I
only want to sum the amounts of one ID, but I am not sure how to sum the
amounts of multiple IDs using one formula. If I want to sum the amounts
of
both 1340 and 5450, I am currently using:

=sumproudct(--(ID=1340),amt)+sumproduct(--(ID=5450,amt)
=(118)+(98)
=216

This works fine if I am only using a couple criteria, but my formulas are
getting ridiculously long (on the edge of being too long for Excel) and
are
becoming difficult to adjust.

Does it help any to know that he formula above can be replaced with this
equivalent?

=SUMPRODUCT(--(ID={1340,5450})*Amt)

Notice the various IDs are simply listed in a comma separated list
surrounded by curly braces.

Rick

 

[Rick Rothstein \(MVP - VB\)]

=SUMPRODUCT(--(ID={1340,5450})*Amt)

Actually, because we are multiplying the two item, the double unary is not
needed (the multiplication takes care of coercing the Boolean values to
their numerical equivalents)...

=SUMPRODUCT((ID={1340,5450})*Amount)

Rick

 

[Harlan Grove]

=SUMPRODUCT((ID={1340,5450})*Amount)

Which is the best way to do it in this case since ID could only equal one or
the other. More generally, multiple OR conditions could be true, in which
case you'd need

=SUMPRODUCT(--((Condition1)+(Condition2)+...+(ConditionN)>0),values)

 

[watchtower]

Thanks, that works the best. I have multiple conditions, so the prior does
not work.