SUMPRODUCT
 

Hello,
In this worksheet all the details of every invoice are shown in columns:
invoice value, weight, destination, customer, etc. There is 1 row per invoice.
Column A shows in which period this invoice is, e.g. Period 2 (1 year = 13
periods of 4 weeks).

In the reporting for these results, the periods are horizontally, with the
details vertically.
I use the formula:
=SUMPRODUCT(--($A$2:$A60=L$66),--($U$2:$U60=$C67),$F$2:$F60).
A is the period (has to be the same as L66, €œPeriod 9€� ; K66 is €œPeriod 8€�
etc)
U is the destination (has to match the destination I write in C67:
€œSenegal€�; C68 is €œHolland€� etc)
F is the invoice value, the result I need.
So this formula gives me the total invoice value per destination per period.

I want to add another criteria now: in column AB, it shows if that customer
is part of the group or not: it has written in it €œGroup€� or €œ3rd party€�.
How do I get the invoice value per destination per period, but only for the
group, and the same for the 3rd parties?

Thank you very much,
Kristoff

SUMPRODUCT
 

Add one more condition.

=SUMPRODUCT(--($A$2:$A60=L$66),--($U$2:$U60=$C67)
,--($AB$2:$AB60="Group"),$F$2:$F60)

If this post helps click Yes
---------------
Jacob Skaria


"ksel" wrote:

Hello,
In this worksheet all the details of every invoice are shown in columns:
invoice value, weight, destination, customer, etc. There is 1 row per invoice.
Column A shows in which period this invoice is, e.g. Period 2 (1 year = 13
periods of 4 weeks).

In the reporting for these results, the periods are horizontally, with the
details vertically.
I use the formula:
=SUMPRODUCT(--($A$2:$A60=L$66),--($U$2:$U60=$C67),$F$2:$F60).
A is the period (has to be the same as L66, €œPeriod 9€� ; K66 is €œPeriod 8€�
etc)
U is the destination (has to match the destination I write in C67:
€œSenegal€�; C68 is €œHolland€� etc)
F is the invoice value, the result I need.
So this formula gives me the total invoice value per destination per period.

I want to add another criteria now: in column AB, it shows if that customer
is part of the group or not: it has written in it €œGroup€� or €œ3rd party€�.
How do I get the invoice value per destination per period, but only for the
group, and the same for the 3rd parties?

Thank you very much,
Kristoff

SUMPRODUCT
 

Thank you very much, that's exactly what I need!
(I tried to add a condition, but I must have done something wrong)

How come you know so much about this? And so fast! Are you working
professionally with Excel?



"Jacob Skaria" wrote:

Add one more condition.

=SUMPRODUCT(--($A$2:$A60=L$66),--($U$2:$U60=$C67)
,--($AB$2:$AB60="Group"),$F$2:$F60)

If this post helps click Yes
---------------
Jacob Skaria


"ksel" wrote:

Hello,
In this worksheet all the details of every invoice are shown in columns:
invoice value, weight, destination, customer, etc. There is 1 row per invoice.
Column A shows in which period this invoice is, e.g. Period 2 (1 year = 13
periods of 4 weeks).

In the reporting for these results, the periods are horizontally, with the
details vertically.
I use the formula:
=SUMPRODUCT(--($A$2:$A60=L$66),--($U$2:$U60=$C67),$F$2:$F60).
A is the period (has to be the same as L66, €œPeriod 9€� ; K66 is €œPeriod 8€�
etc)
U is the destination (has to match the destination I write in C67:
€œSenegal€�; C68 is €œHolland€� etc)
F is the invoice value, the result I need.
So this formula gives me the total invoice value per destination per period.

I want to add another criteria now: in column AB, it shows if that customer
is part of the group or not: it has written in it €œGroup€� or €œ3rd party€�.
How do I get the invoice value per destination per period, but only for the
group, and the same for the 3rd parties?

Thank you very much,
Kristoff