I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

Nel post
*Toto* ha scritto:

I need a formula to find how many productA(f.ex. apples) have I sold
at the same time as i have sold productB (f.ex bananas) (with
identical invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

You can use Autofilter, filtering on the column with invoice number


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thanks for your help. I am sorry, but may be I haven't raised my question
clear.
Some invoices have both product A and product B, some have only product A or
B.
I am looking for the salesamount for product A only when it is sold together
with product B (there are 15.000 lines...). Perhaps I can extend my chart
with a column with a function that return the amount if the criteria in the
function is right..? But how ??

Toto :-)

Toto skrev:

I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

Nel post
*Toto* ha scritto:

Thanks for your help. I am sorry, but may be I haven't raised my
question clear.
Some invoices have both product A and product B, some have only
product A or B.
I am looking for the salesamount for product A only when it is sold
together with product B (there are 15.000 lines...). Perhaps I can
extend my chart with a column with a function that return the amount
if the criteria in the function is right..? But how ??

Toto :-)

Toto skrev:

I need a formula to find how many productA(f.ex. apples) have I sold
at the same time as i have sold productB (f.ex bananas) (with
identical invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

Maybe you can try to use a Pivot Table. It's a very good way to summarize
informations.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

=SUMPRODUCT((J1:J9=50)*(K1:K9={"apples","bananas"} )*L1:L9)

--
Don Guillett
SalesAid Software

"Toto" wrote in message
...
I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

Try this as an array formula (enter with Ctrl+Shift+Enter)

=IF(COUNTIF($A$2:$A$10,50)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10="apples")*($A$2:$A$10=50),0)),"")

Looking for "apples" in Invoice 50 by checking invoice count. If it is 1,
returns blank.

You could change hold the parameters (invoivce number and product in a cell
(e.g. X1 & X2).

=IF(COUNTIF($A$2:$A$10,X1)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10=X2)*($A$2:$A$10=X1),0)),"")

HTH

"Toto" wrote:

I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

Thank you for the formula, but there is a problem; it returns also the amount
of the bananas if there are more than one occurrence of the invoicenumber.
At the end of the formula you wrote ,0)),"") . When I try to use this
formula, it changes to *0)),""). What does this mean?? Could this be the
reason ?

I would appreciate your answer...
Toto




Toppers skrev:

Try this as an array formula (enter with Ctrl+Shift+Enter)

=IF(COUNTIF($A$2:$A$10,50)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10="apples")*($A$2:$A$10=50),0)),"")

Looking for "apples" in Invoice 50 by checking invoice count. If it is 1,
returns blank.

You could change hold the parameters (invoivce number and product in a cell
(e.g. X1 & X2).

=IF(COUNTIF($A$2:$A$10,X1)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10=X2)*($A$2:$A$10=X1),0)),"")

HTH

"Toto" wrote:

I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

I got the following results in the 'formula 'column using:

=IF(AND(COUNTIF($A$2:$A$10,$A2)1,$B2="apples"),IN DEX($C$2:$C$10,MATCH(1,($B$2:$B$10="apples")*($A$2 :$A$10=$A2),0)),"")

entered as an array formula.

invoice product kg formula
50 apples 7 7
50 bananas 2
51 apples 1
52 apples 1
53 apples 4 4
53 bananas 2
54 bananas 2
55 apples 3 3
55 bananas 2


You should not get the *0 in the formula.


hth


"Toto" wrote:

Thank you for the formula, but there is a problem; it returns also the amount
of the bananas if there are more than one occurrence of the invoicenumber.
At the end of the formula you wrote ,0)),"") . When I try to use this
formula, it changes to *0)),""). What does this mean?? Could this be the
reason ?

I would appreciate your answer...
Toto




Toppers skrev:

Try this as an array formula (enter with Ctrl+Shift+Enter)

=IF(COUNTIF($A$2:$A$10,50)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10="apples")*($A$2:$A$10=50),0)),"")

Looking for "apples" in Invoice 50 by checking invoice count. If it is 1,
returns blank.

You could change hold the parameters (invoivce number and product in a cell
(e.g. X1 & X2).

=IF(COUNTIF($A$2:$A$10,X1)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10=X2)*($A$2:$A$10=X1),0)),"")

HTH

"Toto" wrote:

I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00

simpler formula ...

=IF(AND(COUNTIF($A$2:$A$10,$A2)1,$B2="apples"),IN DEX($C$2:$C$10,MATCH($A2,$A$2:$A$10,0)),"")

Entered with Ctrl+Shift+Enter

"Toppers" wrote:

I got the following results in the 'formula 'column using:

=IF(AND(COUNTIF($A$2:$A$10,$A2)1,$B2="apples"),IN DEX($C$2:$C$10,MATCH(1,($B$2:$B$10="apples")*($A$2 :$A$10=$A2),0)),"")

entered as an array formula.

invoice product kg formula
50 apples 7 7
50 bananas 2
51 apples 1
52 apples 1
53 apples 4 4
53 bananas 2
54 bananas 2
55 apples 3 3
55 bananas 2


You should not get the *0 in the formula.


hth


"Toto" wrote:

Thank you for the formula, but there is a problem; it returns also the amount
of the bananas if there are more than one occurrence of the invoicenumber.
At the end of the formula you wrote ,0)),"") . When I try to use this
formula, it changes to *0)),""). What does this mean?? Could this be the
reason ?

I would appreciate your answer...
Toto




Toppers skrev:

Try this as an array formula (enter with Ctrl+Shift+Enter)

=IF(COUNTIF($A$2:$A$10,50)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10="apples")*($A$2:$A$10=50),0)),"")

Looking for "apples" in Invoice 50 by checking invoice count. If it is 1,
returns blank.

You could change hold the parameters (invoivce number and product in a cell
(e.g. X1 & X2).

=IF(COUNTIF($A$2:$A$10,X1)1,INDEX($C$2:$C$10,MATC H(1,($B$2:$B$10=X2)*($A$2:$A$10=X1),0)),"")

HTH

"Toto" wrote:

I need a formula to find how many productA(f.ex. apples) have I sold at the
same time as i have sold productB (f.ex bananas) (with identical
invoicenumber), f.ex.:
invoiceno. product kg
50 apples 1,00
50 bananas 2,00
51 apples 1,00
52 apples 1,00
53 apples 1,00
53 bananas 2,00
54 bananas 2,00
55 apples 1,00
55 bananas 2,00