# Find total quantity sold of each product

Copper Contributor

# Find total quantity sold of each product

Hi everyone,
I have product name in columns: A, C, E, D, G, I, K, M, O, Q, S, U, W, Y, AA, AC, AE, AG, AI, AK, AM, AO, AQ, AS, AU, AW, AY, BA, BC, BE, BG, and in columns B, D, F, H, J, L, N, P, R, T, V, X, Z, AB, AD, AF, AH, AJ, AL, AN, AP, AR, AT, AV, AX, AZ, BB, BD, BF, BH I have quantities sold. In the column "BI" I listed all product names.
In the column BJ(next to BI) I want to see total quantity sold of each product. I tried to use SUMIF but failed to get the result I wanted. I believe I need this formula to solve my issue: SUMIF(range, criteria, [sum_range])

small example:

The formula should lookup values and sum up matching ones.

6 Replies

# Re: Find total quantity sold of each product

``=SUMIF(\$A\$2:\$E\$4,H2,\$B\$2:\$F\$4)``

# Re: Find total quantity sold of each product

I want to second the observation made by @Detlef_Lewin : That is a bad setup.

WHY do you have your data arrayed in that fashion?

If you're getting it from somebody else that way, then that would be an understandable answer to the question of "Why?" But if you created it, what did you have in mind? And if you're planning to continue to track products and sales, please take the time to reorganize it.

If, for example, each pair of columns represents a time period (be it week, month, or quarter), just add a column that contains date, and then the two columns of product and quantity, going on and on, with new rows for each day or week (depending on the level of granularity needed in the analysis). BUT do not continue with it as is. All that would do--as you have discovered--is make it harder to actually analyze the data. It's possible, but there's no reason to get in Excel's way.

Organized as I'm suggesting, in fact, would make it possible for the Pivot Table tool to do all the work for you. Not a single formula would be required. Here's a link that explains the Pivot Table.

# Re: Find total quantity sold of each product

How about convert multiple columns to multiple rows and then sum group by products?

# Re: Find total quantity sold of each product

so what I want to achieve is that I want to be able to track how much of each product I sell daily and at the end of the month to have total values.
e.g.
product name total sold
prod a 5
prod b 6

But have no idea how to achieve it.

I also have a problem configuring In stock, In stock changes and sold columns. At the end of the working day I remove values from sold column as tmr is a new day and new sales will occur. but in order to track how much of each product left I manually subtract from In stock chnages - sold. For instance, as you can see in the short video, Product A, in stock = 10, sold = 2, In stock changes = 8. so 8 units of Prod A is left, and the next day I will subtrack sold from In stock chnages which is 8 for now, in this case, I can see how much of Product A I had initially and how much left after each working day.

I know this is a wrong approach, I came up with this BS:))) I just do not know how to do it properly.

# Re: Find total quantity sold of each product

I have attached a new screenshot to my original post

So what I want to achieve is that I want to be able to track how much of each product I sell daily and at the end of the month to have total values.
e.g.
product name total sold
prod a 5
prod b 6

But have no idea how to achieve it.

I also have a problem configuring In stock, In stock changes and sold columns. At the end of the working day I remove values from sold column as tmr is a new day and new sales will occur. but in order to track how much of each product left I manually subtract from In stock chnages - sold. For instance, as you can see in the short video, Product A, in stock = 10, sold = 2, In stock changes = 8. so 8 units of Prod A is left, and the next day I will subtrack sold from In stock chnages which is 8 for now, in this case, I can see how much of Product A I had initially and how much left after each working day.

I know this is a wrong approach, I came up with this BS:))) I just do not know how to do it properly.

# Re: Find total quantity sold of each product

That new image shows a date column that is blank. Start using it and then let the Pivot Table do your daily summaries. I gave you a link explaining the Pivot Table