Unique values

  • Thread starter Thread starter ksel
  • Start date Start date
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
 
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?
Click to expand...

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.
 
Back
Top