Thread: Sales Order and Report Excel Thanks: 0 Likes: 0

1. Re: Sales Order and Report Excel

With sheet 1 like

Excel 2013/2016
ABCD
1Date Customer Name Product Code Qty
228/05/2019Customer 1Product 51
310/06/2019Customer 2Product 41
415/07/2019Customer 1Product 51

Sheet1

Shhet2

Excel 2013/2016
ABCD
1Product OrdersCustomer 1Customer 2Customer 3
2Product 1000
3Product 2000
4Product 3000
5Product 4010
6Product 5200
7Product 6000
8Product 7000
9Product 8000
10Product 9000
11Product 10000
12Product 11000

Sheet2

Worksheet Formulas
CellFormula
B2=SUMIFS(Sheet1!\$D\$2:\$D\$4,Sheet1!\$C\$2:\$C\$4,\$A2,Sheet1!\$B\$2:\$B\$4,B\$1)

2. Re: Sales Order and Report Excel

Originally Posted by sandy666
How about PowerQuery (Get&Transform) or you want to stay with formula(s)?
Hi as I want to be more easy for the rest people of the company I believed that formulas or VBA was better.
I do not kn ow how to use Power Query, if you can help me in either way will be great.
thanks

3. Re: Sales Order and Report Excel

you got formula solution from Fluff so it should be easy

edit:
anyway, what is your Excel version? and everyone there has the same version?

4. Re: Sales Order and Report Excel

Originally Posted by Fluff
Worksheet Formulas
Cell Formula
B2 =SUMIFS(Sheet1!\$D\$2:\$D\$4,Sheet1!\$C\$2:\$C\$4,\$A2,Sheet1!\$B\$2:\$B\$4,B\$1)
This solution seems to work.
Thanks,
What about if in row1 and coumn A instead of text has formulas that read all the customers from another sheet abd alll products from another sheet. Will the formula work or has to be text in column a and row 1 to work? I mean in row A3 insted of "Custoner2" I have =Sheet4!A5 which is the sheet that has in all coumn A all the customers.

5. Re: Sales Order and Report Excel

It should work quite happily if there is a formula in there rather than text

Also please do not quote entire posts, as it just clutters up the board