Sum formula with criteria - Letters not working

[Gonney]

Hi Team,
I thought i had this all figured out and turns out my formula does not like letters and numbers combined. Can you guys have a quick look and see if there is any feedback/solution could suggest?

=SUM(FILTER($A$2:$A$800, ((LEFT($B$2:$B$800,1)="1")*($C$2:$C$800="A")) + (($B$2:$B$800="NF*") + ($B$2:$B$800="NA*") + ($B$2:$B$800="NE*") + ($B$2:$B$800="NC*"))))

Current formula finds the first cell being 1, but does not pick up any further cells with NF/NA/NE etc in them. With Numbers it's only important to capture the first one with letters first 2 should be ok.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Amount	Reference	Locaion		Amount
2	10	1234	A		10	<--- looking for formula
3	10	NC1234	A			<--- looking for formula			To be grouped together
4	10	NF1234	A			<--- looking for formula			1234, NC, NF
5	20	3221	B			<--- looking for formula			3221
6	50	456	B			<--- looking for formula			456
7	10	221	A			<--- looking for formula			221 A
8	15	221	B			<--- looking for formula			221 B
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUM(FILTER($A$2:$A$800, ((LEFT($B$2:$B$800,1)="1")*($C$2:$C$800="A")) + (($B$2:$B$800="NF*") + ($B$2:$B$800="NA*") + ($B$2:$B$800="NE*") + ($B$2:$B$800="NC*"))))

 

[Peter_SSs]

It is not clear to me what your desired results would be. For that sample data, could you fill them in manually and post again with XL2BB together with any further written clarification?

 

[Gonney]

The desired outcome is to have a formula that adds the total of A2:A8 together if the reference and location is the same - Example with 1234 we are just looking for the 1 to be captured as the first (as there are multiple different numbers that all start with different things) and also have NC1234 + NF1234 also captured, the important part of that is the NC and NF from a criteria aspect.

Uploaded new table:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Amount	Reference	Locaion		Expected outcome	Formula goal
2	10	1234	A		30	123 + NC +NF at Location A	<--- looking for formula
3	10	NC1234	A		20	3221 at Location B	<--- looking for formula		To be grouped together
4	10	NF1234	A		50	456 at Location B	<--- looking for formula		1234, NC, NF
5	20	3221	B		10	221 at A	<--- looking for formula		3221
6	50	456	B		15	221 at B	<--- looking for formula		456
7	10	221	A				<--- looking for formula		221 A
8	15	221	B				<--- looking for formula		221 B
Sheet1