way to add figures

[davidmor]

hi i have a report and i need to total the numbers in column s, i need to use the ship request number from column m, this has 6 digits then / and another number.

is there a way excel can sum all the same ship request and give me the total figure for column S

ie if 327479 had more than one occurrence it would add all the numbers from "S" and return a total

hope this makes sense.

Thanks

 

[Fluff]

Do you want to sum col S based on the 1st 6 digits in col M or the entire string in col M?

 

[davidmor]

Hi Fluff

its just the first 6 digits, problem is i dont have a list of these first 6 digits so hopefully would want excel to report those as well

 

[Fluff]

Ok how about

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V
1
2	123456/1						1		123456	6
3	123456/2						5		234567	3
4	234567/7						3
5
6
Main

Cell Formulas
Range	Formula
U2:U3	U2	=UNIQUE(FILTER(LEFT(M2:M100,6),M2:M100<>""))
V2:V3	V2	=SUMIFS(S:S,M:M,U2#&"*")
Dynamic array formulas.

 

[davidmor]

Ok how about

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V
1
2	123456/1						1		123456	6
3	123456/2						5		234567	3
4	234567/7						3
5
6
Main

Cell Formulas
Range	Formula
U2:U3	U2	=UNIQUE(FILTER(LEFT(M2:M100,6),M2:M100<>""))
V2:V3	V2	=SUMIFS(S:S,M:M,U2#&"*")
Dynamic array formulas.

Hi Fluff,

Thats worked great thanks, ive just remembered i also need it to report the date from column d what would i do there ?

i just need the date once from each ship req

Thanks

 

[Fluff]

How about

=INDEX(D2:D100,MATCH(U2#&"*",M2:M100,0))

 

[davidmor]

How about

=INDEX(D2:D100,MATCH(U2#&"*",M2:M100,0))

HI Fluff,

ive tried that but i keep getting random 5 digit numbers, ive changed the "U" in your formula to BJ as thats the cell im looking at but cant get it to show correct info

=INDEX(D2:D1000,MATCH(BJ2#&"*",M2:M1000,0))

this is what i get

ive hidden all unused info and this is what im left with, so BL should be reporting whats in D

 

[Fluff]

Just format the cells as Dates.

 

[davidmor]

Just format the cells as Dates.

Thanks, now i feel stupid lol

 

[Fluff]

You're welcome & thanks for the feedback.