Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks:
Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
Hi Kristoff
You need a SumProduct formula. Suppose you have periods in A2:A100, Customers in B2:B100 and Weight sold in C2:C100, the formula below will calculate the weight sold in period 1 to customer 1. =SUMPRODUCT(--(A2:A100="Period 1"),--(B2:B100="cmr 1"),C2:C100) You can of course replace "Period 1" and "cmr 1" with a cell reference to make the formula more flexible. Hopes this helps. --- Per "ksel" skrev i meddelelsen ... Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks: Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
Try with SUMPRODUCT
=SUMPRODUCT(--(A1:A100="Period"),--(B1:B100="customer"),C1:C100) If this post helps click Yes --------------- Jacob Skaria "ksel" wrote: Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks: Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
This is exactly what I was looking for!
Thanks a lot Per! Kristoff "Per Jessen" wrote: Hi Kristoff You need a SumProduct formula. Suppose you have periods in A2:A100, Customers in B2:B100 and Weight sold in C2:C100, the formula below will calculate the weight sold in period 1 to customer 1. =SUMPRODUCT(--(A2:A100="Period 1"),--(B2:B100="cmr 1"),C2:C100) You can of course replace "Period 1" and "cmr 1" with a cell reference to make the formula more flexible. Hopes this helps. --- Per "ksel" skrev i meddelelsen ... Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks: Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
Check out the below link..(unary minus operator)
http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "ksel" wrote: Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks: Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
Thanks a lot Jacob!
What are the hyphens in the formula for? "(--(A1..." "Jacob Skaria" wrote: Try with SUMPRODUCT =SUMPRODUCT(--(A1:A100="Period"),--(B1:B100="customer"),C1:C100) If this post helps click Yes --------------- Jacob Skaria "ksel" wrote: Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks: Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if 2 variables
Perfect - thanks a lot!
What are the hypens in the formula for? (--(A1... "Jacob Skaria" wrote: Try with SUMPRODUCT =SUMPRODUCT(--(A1:A100="Period"),--(B1:B100="customer"),C1:C100) If this post helps click Yes --------------- Jacob Skaria "ksel" wrote: Hi, in 1 column I have the name of periods (1 year = 13 periods of 4 weeks: Period 1, Period 2 etc) and in another column I have the name of customers (cmr 1, cmr 2 etc). A third column has the weight of product sold to that customer. Now I would like to see how much weight was sold to every customer per period: if you see "Period 1" in column 1, count weight if you see "cmr 1". In the result, the customer's name is sorted vertically, the periods horizontally... I tried with SUMIF, but then I have to change the ranges for every period... Thanks a lot for suggestions, Kristoff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
Three Variables | Excel Worksheet Functions | |||
Too many variables? | Excel Worksheet Functions | |||
Using variables in a name | Excel Discussion (Misc queries) | |||
SUM IF and two variables | Excel Worksheet Functions |