Help Running Tally

[PFS12]

80-20 Sheet trial.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
15	HUIZI003	48140938	03	NWO		Q19944	BRUSH,CIRC	20210108	20210108	-138.96	18	CASE07	-272.16
16	HUIZI003	48140938	03	NWO		Q19944	BRUSH,CIRC	20210108	20210108	138.96	18	CASE07	216
17	BELLE003	AE3067	07	GAR		P68836	HARNESS	20210111	20210111	464.1	696.15	AGEX01	696.15
18	MURRO001	541601	07	GAR		P68837	BANDIT CIH R	20210111	20210111	832	130	YIEL01	1248
19	MURRO001	541600	07	GAR		P68837	BANDIT CIH L	20210111	20210111	832	130	YIEL01	1248
20	MAGOS001	84475542	07	GAR		P68870	FILTER, ENGINE	20210112	20210112	26.54	18.5	CASE07	37
21	MAGOS001	86982522	07	GAR		P68870	FILTER, AIR	20210112	20210112	57.96	54.25	CASE07	108.5
22	MAGOS001	86982523	07	GAR		P68870	FILTER, AI	20210112	20210112	35.24	25.25	CASE07	50.5
Sheet1

Good afternoon, The above sheet is an excerpt of data that i pull monthly. I'd like to see if I can start to glean out dollars throughout the course of the year by customer. The information I need is is column A (customer) and Column M (Sell price). Is there a way to do monthly data dumps into one sheet, have excel look for the customer name, if it already exists or not put it into another column and total everything for the year in the new columns. The above example should tally

HUIZI003 -56.16
BELLE003 696.15
MURRO001 2496
MAGOS001 196

Next month these customers might buy something again, and I would like it to tally them and add to existing. as well as add new customers if needed. THis might be a big ask, but its definitely over my head. Please aske me a question if my explanation isn't good.

Thanks,

Seth

 

[gigidica]

Did you try consolidating the data into a pivot table?

 

[Saba Sabaratnam]

Or you could set Summary sheet like this and use formulas. I have your monthly data in another sheet named "Monthly Data".

Enter the following formula in A2 and copy it down (maximum 1000 rows) in Summary sheet

=IFERROR(INDEX('MONTNLY DATA'!$A$1:$A$1000,MATCH(0,INDEX(COUNTIFS(SUMMARY!$A$1:A1,'MONTNLY DATA'!$A$1:$A$1000),0,1),0)),"")

The above formula will pick all unique customer names and list in Summary sheet.

And then use the following formula in B2 in Summary sheet to sum total sales for the month.

=SUMIFS('MONTNLY DATA'!M:M,'MONTNLY DATA'!A:A,SUMMARY!A2)

Kind regards

Saba

 

[PFS12]

Did you try consolidating the data into a pivot table?

I would love to but I am unfamiliar with how to set up a pivot table to do so.

 

[PFS12]

Or you could set Summary sheet like this and use formulas. I have your monthly data in another sheet named "Monthly Data".

Enter the following formula in A2 and copy it down (maximum 1000 rows) in Summary sheet

=IFERROR(INDEX('MONTNLY DATA'!$A$1:$A$1000,MATCH(0,INDEX(COUNTIFS(SUMMARY!$A$1:A1,'MONTNLY DATA'!$A$1:$A$1000),0,1),0)),"")

The above formula will pick all unique customer names and list in Summary sheet.

And then use the following formula in B2 in Summary sheet to sum total sales for the month.

=SUMIFS('MONTNLY DATA'!M:M,'MONTNLY DATA'!A:A,SUMMARY!A2)

View attachment 41116

Kind regards

Saba

Saba this seems simple enough but its not copying correctly. I have seen some of the misspellings and am trying to correct them but its not working. I'm sure its something I am doing wrong. Also the 1000 lines will get eaten up rather quickly I am afraid.

 

[PFS12]

Saba this seems simple enough but its not copying correctly. I have seen some of the misspellings and am trying to correct them but its not working. I'm sure its something I am doing wrong. Also the 1000 lines will get eaten up rather quickly I am afraid.

Could you do this in xl2BB?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1																Customer
2	HUIZI003	48140938	3	NWO		Q19944	BRUSH,CIRC	20210108	20210108	-138.96	18	CASE07	-272.16			HUIZI003	-56.16
3	HUIZI003	48140938	3	NWO		Q19944	BRUSH,CIRC	20210108	20210108	138.96	18	CASE07	216			BELLE003	696.15
4	BELLE003	AE3067	7	GAR		P68836	HARNESS	20210111	20210111	464.1	696.15	AGEX01	696.15			MURRO001	2496
5	MURRO001	541601	7	GAR		P68837	BANDIT CIH R	20210111	20210111	832	130	YIEL01	1248			MAGOS001	196
6	MURRO001	541600	7	GAR		P68837	BANDIT CIH L	20210111	20210111	832	130	YIEL01	1248
7	MAGOS001	84475542	7	GAR		P68870	FILTER, ENGINE	20210112	20210112	26.54	18.5	CASE07	37
8	MAGOS001	86982522	7	GAR		P68870	FILTER, AIR	20210112	20210112	57.96	54.25	CASE07	108.5
9	MAGOS001	86982523	7	GAR		P68870	FILTER, AI	20210112	20210112	35.24	25.25	CASE07	50.5
10
11
12
Lists

Cell Formulas
Range	Formula
P2:P9	P2	=IFERROR(INDEX($A$2:$A$5000,AGGREGATE(15,6,(ROW($A$2:$A$5000)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$5000,P$1:P1,0)))/($A$2:$A$5000<>""),1)),"")
Q2:Q9	Q2	=IF(P2="","",SUMIFS(M:M,A:A,P2))

 

[PFS12]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1																Customer
2	HUIZI003	48140938	3	NWO		Q19944	BRUSH,CIRC	20210108	20210108	-138.96	18	CASE07	-272.16			HUIZI003	-56.16
3	HUIZI003	48140938	3	NWO		Q19944	BRUSH,CIRC	20210108	20210108	138.96	18	CASE07	216			BELLE003	696.15
4	BELLE003	AE3067	7	GAR		P68836	HARNESS	20210111	20210111	464.1	696.15	AGEX01	696.15			MURRO001	2496
5	MURRO001	541601	7	GAR		P68837	BANDIT CIH R	20210111	20210111	832	130	YIEL01	1248			MAGOS001	196
6	MURRO001	541600	7	GAR		P68837	BANDIT CIH L	20210111	20210111	832	130	YIEL01	1248
7	MAGOS001	84475542	7	GAR		P68870	FILTER, ENGINE	20210112	20210112	26.54	18.5	CASE07	37
8	MAGOS001	86982522	7	GAR		P68870	FILTER, AIR	20210112	20210112	57.96	54.25	CASE07	108.5
9	MAGOS001	86982523	7	GAR		P68870	FILTER, AI	20210112	20210112	35.24	25.25	CASE07	50.5
10
11
12
Lists

Cell Formulas
Range	Formula
P2:P9	P2	=IFERROR(INDEX($A$2:$A$5000,AGGREGATE(15,6,(ROW($A$2:$A$5000)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$5000,P$1:P1,0)))/($A$2:$A$5000<>""),1)),"")
Q2:Q9	Q2	=IF(P2="","",SUMIFS(M:M,A:A,P2))

This seems to work, Can I adjust the Index to a number bigger than 5000 because I'll probably have more than 5000 lines by the end of the year.

Thanks!

 

[Fluff]

Yup, just change the numbers of rows to what ever you need.