Unique values

K

ksel

Hi,
I have a list of container shipments to different countries, 1 line per
invoice:
France 1
Belgium 2
Spain 4
France 3
Belgium 4
In the reporting, it counts the total per destination: 4 to France, 6 to
Belgium, 4 to Spain.
To do that, I type "France" and then all containers with that destination
are counted. Is it possible to avoid the typing, so that if a new destination
is added (Brazil 5), "Brazil" is seen as new to the list and copied in the
reporting?
Thanks a lot,
Kristoff
 
H

Harlan Grove

ksel said:
I have a list of container shipments to different countries, 1 line per
invoice:
France 1
Belgium 2
Spain 4
France 3
Belgium 4
In the reporting, it counts the total per destination: 4 to France, 6 to
Belgium, 4 to Spain.
To do that, I type "France" and then all containers with that destination
are counted. Is it possible to avoid the typing, so that if a new destination
is added (Brazil 5), "Brazil" is seen as new to the list and copied in the
reporting?

If the table of invoices were named INVOICE_TBL and the top-left cell
in the results range were E2, one approach would be

E2:
=INDEX(INVOICE_TBL,1,1)

E3 [array formula]:
=IF(SUM(F$2:F2)<SUM(INDEX(INVOICE_TBL,0,2)),INDEX(INVOICE_TBL,
MATCH(0,COUNTIF(E$2:E2,INDEX(INVOICE_TBL,0,1)),0),1),"")

Fill E3 down as far as you think you'll need it.

F2:
=IF(E2<>"",SUMIF(INDEX(INVOICE_TBL,0,1),E2,INDEX(INVOICE_TBL,0,2)),"")

Double click on the fill handle to fill it down into the same rows as
there are formulas in col E.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top