Vlookup and sumif formula assist

[sintek]

Hi All

I've posted a very basic spreadsheet to assist with a detailed Debtors Control I'm busy with.

Sheet 1 is purchases and Sheet 2 is a total of all purchases for all clients.
Sheet 1 will be daily transactions and Sheet 2 will be a combined total of all individual daily transactions.
Irrespective of how many different client transactions occur on Sheet 1, Sheet 2 needs to extract the sum of each individual client.

As per file supplied.

Any assistance will be appreciated.

[Aladin Akyurek]

In B1 of the destination sheet enter and copy down:

=SUMIFS(PURCHASES!B:B,PURCHASES!A:A,A1)

[sintek]

Hi Aladin

Thanks for your speedy assistance. Any idea if there is a way to take the names from Purchases sheet and generate the Names in alphabetical order in destination sheet Column A. There could be 1000's transactions with different names.

I thank you in advance.

[sandy666]

Create a unique distinct text list from a range containing both numerical and text values


Copy of PURCHASES RECORD.xlsx
no helper column

[Beamernsw]

This should work, but it uses a helper column in A which you can hide. Just make sure the formula is copied down past the last entry in PURCHASES.

[sintek]

Hi Beamernsw

Works perfectly....Thanks for the lesson.

[sintek]

Hi Sandy 666

Thanks for input, however, does not fulfill the required result.I might not have explained myself.
Beamernsw achieved my result.

Thanks again

[sandy666]

no problem
I lost "Names in alphabetical order"

[Aladin Akyurek]

[...]
Any idea if there is a way to take the names from Purchases sheet and generate the Names in alphabetical order in destination sheet Column A.
[...]

Row\Col	A	B
1	ADAM	10.00
2	BRIAN	15.00
3	CHARLIE	20.00
4	DEVON	10.00
5	ADAM	5.00
6	JON	66.45
7	DEVON	10.00
8	CHARLIE	15.00
9	EDITH	20.00
10	FRAN	30.00
11	CHARLIE	85.00
12	GOLIATH	65.00
13	HENRY	25.00

1. Define PList by means of Formulas | Name Manager as referring to:

Please Login or Register  to view this content.

2. Define Ivec as referring to:

Please Login or Register  to view this content.

3. Switch to TOTAL PURCHASE OF EACH CUSTOMER...

Row\Col	A	B	C	D
1	9
2	uCustomer	Idx	sCustomer	Sales
3	ADAM	1	ADAM	$15.00
4	BRIAN	2	BRIAN	$15.00
5	CHARLIE	3	CHARLIE	$120.00
6	DEVON	4	DEVON	$20.00
7	JON	9	EDITH	$20.00
8	EDITH	5	FRAN	$30.00
9	FRAN	6	GOLIATH	$65.00
10	GOLIATH	7	HENRY	$25.00
11	HENRY	8	JON	$66.45
12

In A1 control+shift+enter, not just enter:

Please Login or Register  to view this content.

In A3 control+shift+enter, not just enter, and copy down:

Please Login or Register  to view this content.

In B3 just enter and copy down:

Please Login or Register  to view this content.

In C3 just enter and copy down:

Please Login or Register  to view this content.

Note. B:C implements the sorting set up, described at:
http://chandoo.org/wp/2008/10/22/sor...sing-formulas/

In D3 just enter and copy down:

Please Login or Register  to view this content.

[sandy666]

in TOTAL PURCHASE OF EACH CUSTOMER A2:

Please Login or Register  to view this content.

with Control+Shift+Enter
where defined name: List = PURCHASE!$A$1:$A$12
no helper column