0

Please look at this screenshot below.

I'm trying to count sum of quantities for sites that have the same first 11 characters. If it's only one it has to be added to the table as well.

Would anyone recommend which formula could help me with that?

Thanks!

enter image description here

shakespeare
  • 126
  • 11
pablitoR
  • 21
  • 1
    It's not a good idea to sum characters, no matter whether there are eleven or just one. –  Apr 26 '17 at 23:46

2 Answers2

0

Add a helper column C, with the formula left(a4,11) in cell C4.

Select the whole table (all three columns) and use it as the data source for a pivot table. Put the helper column on the rows of the pivot table and the Quantity as the value.

MattClarke
  • 1,647
  • 1
  • 11
  • 32
0

I assume the goal here is to sum the quantities in column B, for which the first eleven characters of column A match. You could do with sumif, making use of a wildcard. Formula would be: =sumif($A$4:$A$14,"XXX-CC-VVVV"&*,$B$4:$B$14) In this case the first eleven characters are hardcoded. If you make use of the answer by MattClarke you can refer to the cells you place that function in. "=left(a4,11)". The asterix sign * indicates a wildcard. This wildcard is any character or multitude of characters. If you want to know more about wildcards have a look at this tutorial for example: https://www.deskbright.com/excel/excel-wildcard/

If you want to know how often a certain building is mentioned in your table you can make use of the countif formula =countif($A$4:$A$14,"XXX-CC-VVVV"&*)

Luuklag
  • 3,897
  • 11
  • 38
  • 57