need to add all the same products but don`t know what formula to use

I think this should do it, as long as there aren't any numbers as product codes.
Put this in K2, then drag down.

Formula:

Please Login or Register  to view this content.

(You'll get double results for apples and pears, but I guess that won't be an issue with your real data.)

Edit: the Offset isn't needed:

Formula:

Please Login or Register  to view this content.

Refer also to my post below (post 5)


If you do have number-only product codes, you may need to fall back on adding multiple SUMIFs together:

Formula:

Please Login or Register  to view this content.

Hope that helps.

Yes, you can extend to use more columns. You can just change the last column number (currently 8), so that this part [Column2]:[Column8] becomes [Column2]:[Column12] or whatever you need.

The way it works is this:
SUMIF ( the range you want to check the criteria against , your criteria , the range you want to sum )
Your criteria are in column K, starting with K2.
The range you want to check those against are in columns A, C, E, G - columns 1 to 7 of the Table.
The ranges you want to sum are in cols B, D, F, H - columns 2 to 8 of the Table.


Because your criteria (products) are not numbers, when the formula tries to look up K2 in a column with numbers in it, it fails (invisibly) so just moves on). If you had a product which was a number, the formula might count the 'product number' as a quantity (depending on what all the numbers in the Table are) so that's why this shouldn't be used with Products which are numbers only. Hope that isn't too confusing!


The Offset in my first formula isn't actually needed (sorry - it was a left over from something much more complicated I was trying first, for some reason). This will work just as well:

Formula:

Please Login or Register  to view this content.

The two ranges include everything in all the specified columns: 1-7 and 2-8.
If you want to extend this, just increment both Column7 and Column8, so they become Column11 and Column12, or whatever you need.


However, if you have a lot more information, then it would be better to change the table structure first, as suggested by izzy-a above. I can't download the attachment in post-4 (I get an error), so I've done my own suggestion - see the attached file. You can either arrange the Products in rows and Locations in columns, or vice-versa. As you want to count the Products, I suggest the latter, as you can then use the 'Totals Row' option of the Excel Table to give you totals automatically. I've included all the different options on the sheet so you can see what I mean.

Hope that helps.

You're welcome, glad I could help.