Hello Gurus,
I am trying to do sumif using parts of a cell as criterion.
I have searched online and I know about this formula =SUMIF(Range,"*Part*",SumRange), however I noticed this only works when there is a space between the criterion and other contents of the cell. Eg "Part Excuse" but it does not work if we have it like this "Part/Excuse".
What I need
I have this template (see attached and below). This is the raw data.
Product Code Location Quantity
A01 12345/GAR 5
A02 12346/GAR 8
A03 12346/GAR 4
A04 12345/GAR 1
A02 12347/GAR 9
A01 12346/GAR 5
A04 12348/GAR 8
A01 12347/GAR 7
A02 12348/GAR 9
A03 12345/GAR 3
A04 12347/GAR 2
A01 12348/GAR 5
A02 12348/SUL 4
A03 12347/SUL 7
A01 12346/SUL 9
A02 12347/SUL 8
A03 12346/SUL 7
A04 12347/SUL 6
A02 12345/SUL 5
A01 12348/SUL 3
A04 12348/SUL 4
A01 12345/SUL 6
A03 12345/SUL 5
A04 12346/SUL 7
In my raw data, I have products with unique codes (here A01, A02, A03, and A04) with quantites but in different locations with unique codes (12345, 12346, 12347, and 12348). But each location has two or more sub-locations (GAR and SUL), therefore each sub-location is described as Location/Sublocation (12345/GAR, 12345/SUL, 12346/GAR, 12346/SUL, 12347/GAR, 12347/SUL, 12348/GAR and 12348/SUL).
This data is automatically generated, so I cannot change the location code by putting space in between, besides the data is large.
I need a formula that will be able to sum the data based on the product code and location code only (irrespective of the sub-location), that is, the location codes 12347/SUL and 12347/GAR should be generated in the same cell by reading only their common code "12347".
Please find attached to see expected result.
Thanks.
Bookmarks