Good morning all,
I currently have a worksheet which I use to log quote requests received. For reasons that are beneficial elsewhere if there are multiple items on the quote for the same company each item is added to a new row the log but using the same identifying order number.
i.e
Status Order Number company item Value Quote Date Order Date
Won 1111.2222 abc123 123abc £100 1/5/18 1/6/18
Won 1111.2222 abc123 345def £100 1/5/18 1/6/18
Won 1111.2222 abc123 678ghi £125 1/5/18 1/6/18
I have now inserted a simple summary sheet which as I want to view the quotes raised and orders won by week. The summary sheet comprises of:-
B1 = week commencing date
Column A = list of items
Column B = count of quotes raised =COUNTIFS('Sales Data'!G:G,'Overview Weekly'!$B$1,'Sales Data'!O:O,A5)
Column C = count of quotes won =COUNTIFS('Sales Data'!G:G,'Overview Weekly'!$B$1,'Sales Data'!O:O,A5)
The issue I have is that the statements above are counting individual entries where there has been a date entered and not individual quote\order numbers, therefore the value returned using on the current statements on the above example would be 3 quotes raised and 3 orders won when the reality is 1 quote was raised and 1 order was won.
I would also like to add a column which gives me the value of the orders won for a particular week and am unable to use vlookup against a list of item as I have differing pricing per customer so need to return a value based on the value of the order for required week selected.
Hope this makes sense, I will try to clarify if not..
Thank you
I currently have a worksheet which I use to log quote requests received. For reasons that are beneficial elsewhere if there are multiple items on the quote for the same company each item is added to a new row the log but using the same identifying order number.
i.e
Status Order Number company item Value Quote Date Order Date
Won 1111.2222 abc123 123abc £100 1/5/18 1/6/18
Won 1111.2222 abc123 345def £100 1/5/18 1/6/18
Won 1111.2222 abc123 678ghi £125 1/5/18 1/6/18
I have now inserted a simple summary sheet which as I want to view the quotes raised and orders won by week. The summary sheet comprises of:-
B1 = week commencing date
Column A = list of items
Column B = count of quotes raised =COUNTIFS('Sales Data'!G:G,'Overview Weekly'!$B$1,'Sales Data'!O:O,A5)
Column C = count of quotes won =COUNTIFS('Sales Data'!G:G,'Overview Weekly'!$B$1,'Sales Data'!O:O,A5)
The issue I have is that the statements above are counting individual entries where there has been a date entered and not individual quote\order numbers, therefore the value returned using on the current statements on the above example would be 3 quotes raised and 3 orders won when the reality is 1 quote was raised and 1 order was won.
I would also like to add a column which gives me the value of the orders won for a particular week and am unable to use vlookup against a list of item as I have differing pricing per customer so need to return a value based on the value of the order for required week selected.
Hope this makes sense, I will try to clarify if not..
Thank you
