• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need to sum based on multiple data relationships

A

alicia

New Member
I am looking for some help in utilizing sum function based on multiple data relationships

Below is an example:

data 1 data 2 data 3 qty
a b c 3
a b c 5
a d e 1
f b c 5
f b c 7

so for every instance of a/b/c occurring i would need the total qty summed
in the above there is a total qty of 8 for a/b/c, and a total qty of 12 for f/b/c. I am working with a spreadsheet with over 2000 combinations for the data1/2/3 fields and need the qty for each combination summed while retaining the formatting in 1/2/3.

Can this be done?
 
Hi Alicia ,

It can certainly be done , but I think you are simplifying your data so that it no longer resembles your working data ; either post about 10 rows of actual data , or even better upload your workbook containing real data.

Narayan
 
Sounds like a sumproduct formula could be an option? something of the form:
= sumproduct((data1column="criteria1")*(data2column="criteria2")*(data3column="criteria3")*(qtycoloumn))
or a simplified version
= sumproduct((a:a="a")*(b:b="b")*(c:c="c")*(d:d))

If you set up a list of the criteria, you could then just reference it for each sumproduct to give the totals you need. If you need to pull out each unique combination in the first place to establish the criteria, that can be done as well, but is somewhat more complex. As Narayan suggested, a bit more information would be helpful.
 
Dear Aalicia,

In simple concept, Add new column (Suppose 'F') and concatenate three column values e.g:

=A2&"/"&B2&"/"&C2

In another Column use

=SUMIF(F:F,F2,D:D)

Column 'D' is your Qty.

Hope It will help you!

Thanks & Regards,
CMA Vishal Srivastava
 
You must log in or register to reply here.
Back
Top