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

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

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

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

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

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

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