# Re: Sumproduct on multiple "or" conditions

On 2 Aug, 15:38, watchtower <watchto...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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.herit...@xxxxxxxxxx" wrote:
On 2 Aug, 15:16, watchtower <watchto...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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

Any thoughts?

Thanks,

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- Hide quoted text -

- 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

.

## Relevant Pages

• Re: Rounding Issues
... Mark showed you that when he showed the last SUMPRODUCT formula. ... which sums only those amounts greater than 10. ... ditch SUMIF. ... Say you only wanted to sum the ...
(microsoft.public.excel.worksheet.functions)
• Re: Counting with multiple matching criteria
... Microsoft Excel MVP ... I found it interesting that using SUMPRODUCT instead of SUM did not work ... criteria could be completely removed from the formula and stored in a ...
(microsoft.public.excel.worksheet.functions)
• Re: Stratify Data In a Range
... Bob Phillips explains =sumproduct() in much more detail here: ... "Bernard Liengme" wrote: ... sum of amounts: from 10,000 to 15,000 ...
(microsoft.public.excel.worksheet.functions)
• Re: Sumproduct on multiple "or" conditions
... 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. ... This works fine if I am only using a couple criteria, ...
(microsoft.public.excel.worksheet.functions)
• Re: SumProduct
... Adjust the ranges to match--but you can't use whole columns. ... Bob Phillips explains =sumproduct() in much more detail here: ... I am trying to sum a column based on 2 sets of criteria, ...
(microsoft.public.excel.worksheet.functions)