Re: Sumproduct on multiple "or" conditions
- From: "aidan.heritage@xxxxxxxxxx" <aidan.heritage@xxxxxxxxxx>
- Date: Thu, 02 Aug 2007 07:47:08 -0700
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
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- 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
.
- References:
- Re: Sumproduct on multiple "or" conditions
- From: aidan.heritage@xxxxxxxxxx
- Re: Sumproduct on multiple "or" conditions
- Prev by Date: Re: Pull a Sample from a Population
- Next by Date: Re: Simple lookup problem...
- Previous by thread: Re: Sumproduct on multiple "or" conditions
- Next by thread: Re: Sumproduct on multiple "or" conditions
- Index(es):
Relevant Pages
|
