Trying to find sales figures of certain products…

T

Toto

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
 
F

Franz Verga

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
Click to expand...

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
 
T

Toto

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:
 
F

Franz Verga

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:
Click to expand...

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
 
T

Toppers

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

=IF(COUNTIF($A$2:$A$10,50)>1,INDEX($C$2:$C$10,MATCH(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,MATCH(1,($B$2:$B$10=X2)*($A$2:$A$10=X1),0)),"")

HTH
 
T

Toto

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:
 
T

Toppers

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

=IF(AND(COUNTIF($A$2:$A$10,$A2)>1,$B2="apples"),INDEX($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
 
T

Toppers

simpler formula ...

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

Entered with Ctrl+Shift+Enter
 
Top