Good evening from the UK, and please forgive my first post.
I've got a set of data, which has a number of elements. This is all being used in a dashboard for a client, and right now I'm getting just a tad frustrated.
I have a Table (OrdersPickData) which looks like the following:
<tbody>
</tbody>
I also have a named Range (DelMethod) which consists of the following values (The order is important as it's used in a custom sortlist in VBA):
<tbody>
</tbody>
There are two things I want to achieve:
1. Count the number of lines where the delivery method is in the range DelMethod AND the delivery date = DelDate (a date entered in a cell, 15/02/2017 in this case).
To achieve this I thought I'd use the following function, using CSE to ensure it's an array formula:
{=COUNTIFS(OrdersPickData[DeliveryMethod],DelMethod, OrdersPickData[PromisedDeliveryDate],DelDate)}
The result I expected is 4, but I'm only getting a result of 1. If I change the sort order of the DelMethod range, I get a count of 2. I can directly attribute this to it being alphabetical, but I didn't expect the calculation to stop after only matching the first value it found.
So, I also tried this:
=SUMPRODUCT(COUNTIF(OrdersPickData[DeliveryMethod],DelMethod)) which counts correctly the number of fields in OrdersPickData, but doesn't cross-check to validate if PromisedDeliveryDate also matches.
2. The second thing I want to be able to do, is calculate the quantity allocated, and the quantity picked, using the same filter.
Just to clarify, I need to be finding orders where they are in the DelMethod List AND PromisedDeliveryDate = DelDate
Any help will be most appreciated, as I fear I shall be bald at the end of the evening if I continue.
Thanks in advance
I've got a set of data, which has a number of elements. This is all being used in a dashboard for a client, and right now I'm getting just a tad frustrated.
I have a Table (OrdersPickData) which looks like the following:
DeliveryMethod | AllocatedQty | PromisedDeliveryDate | PickedQty |
CARRIER | 10 | 15/02/2017 | 5 |
CARRIER | 5 | 15/02/2017 | 0 |
DELIVERY - 03 | 10 | 15/02/2017 | 10 |
DELIVERY - 04 | 2 | 15/02/2017 | 0 |
DELIVERY - 03 | 3 | 14/02/2017 | 3 |
<tbody>
</tbody>
I also have a named Range (DelMethod) which consists of the following values (The order is important as it's used in a custom sortlist in VBA):
DELIVERY - 03 |
DELIVERY - 04 |
CARRIER |
COLLECTION |
<tbody>
</tbody>
There are two things I want to achieve:
1. Count the number of lines where the delivery method is in the range DelMethod AND the delivery date = DelDate (a date entered in a cell, 15/02/2017 in this case).
To achieve this I thought I'd use the following function, using CSE to ensure it's an array formula:
{=COUNTIFS(OrdersPickData[DeliveryMethod],DelMethod, OrdersPickData[PromisedDeliveryDate],DelDate)}
The result I expected is 4, but I'm only getting a result of 1. If I change the sort order of the DelMethod range, I get a count of 2. I can directly attribute this to it being alphabetical, but I didn't expect the calculation to stop after only matching the first value it found.
So, I also tried this:
=SUMPRODUCT(COUNTIF(OrdersPickData[DeliveryMethod],DelMethod)) which counts correctly the number of fields in OrdersPickData, but doesn't cross-check to validate if PromisedDeliveryDate also matches.
2. The second thing I want to be able to do, is calculate the quantity allocated, and the quantity picked, using the same filter.
Just to clarify, I need to be finding orders where they are in the DelMethod List AND PromisedDeliveryDate = DelDate
Any help will be most appreciated, as I fear I shall be bald at the end of the evening if I continue.
Thanks in advance