Formula query

[PatrickTT]

Hi,
I am looking for a formula that will sum values in a column if the value in a cell in the same row is either 'received' or 'missing'. Basically I need the total of 'received' and 'missing'
See sample sheet below:

Vendor No.	GR Quantity 2023	Update 31/10/24
1062145	10,968,190	Received
1047261	32,460,908	missing
1054466	20,067,750	missing
AND021	32,320,235	missing
1025333	6,683,736	missing
1000008	22,298,352	missing
CON030	3,608,000	missing
1059410	23,396,900	missing
1064259	5,349,050	missing
63367	228,140	missing
55887	2,506,375	Received

 

[JEC]

=GROUPBY(C2:C12,B2:B12,SUM,,0)

 

[RobP]

Hi,

would this do what you need ?

Book1
	B	C	D	E
2
3	Vendor No.	GR Quantity 2023	Update 31/10/24
4	1062145	10,968,190	Received	146413071
5	1047261	32,460,908	missing
6	1054466	20,067,750	missing
7	AND021	32,320,235	missing
8	1025333	6,683,736	missing
9	1000008	22,298,352	missing
10	CON030	3,608,000	missing
11	1059410	23,396,900	missing
12	1064259	5,349,050	missing
13	63367	228,140	missing
14	55887	2,506,375	Received
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMIF(D4:D14,"missing",C4:C14)

 

[PatrickTT]

thanks

Hi,

would this do what you need ?

Book1
	B	C	D	E
2
3	Vendor No.	GR Quantity 2023	Update 31/10/24
4	1062145	10,968,190	Received	146413071
5	1047261	32,460,908	missing
6	1054466	20,067,750	missing
7	AND021	32,320,235	missing
8	1025333	6,683,736	missing
9	1000008	22,298,352	missing
10	CON030	3,608,000	missing
11	1059410	23,396,900	missing
12	1064259	5,349,050	missing
13	63367	228,140	missing
14	55887	2,506,375	Received
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMIF(D4:D14,"missing",C4:C14)

Thanks - this worked perfectly!

 

[RobP]

Great, glad we could help,

thanks for the feedback...

Cheers
Rob