COUNTIFS using a named Range

[a8_tony]

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:

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

 

[FDibbins]

It can be hard to work out ranges and criteria when using structured tables, can you "translate" that into actual ranges please?

 

[a8_tony]

It can be hard to work out ranges and criteria when using structured tables, can you "translate" that into actual ranges please?

Hi FDibbins,

In this example the function would be:

{=COUNTIFS(A1:A6,F1:F4,C1:C6,J3)}

Thanks (Y)

 

[FDibbins]

COUNTIFS syntax is...
=countifs(criteria-range1,criteria1,criteria-range2,criteria2,criteria-range3,criteria3.........)
Generally, your criteria needs to be a single cell, it looks like your 1st criteria is a range? F1:F4

 

[a8_tony]

It is, but I was under the impression I could use an array as a criteria?

=SUM(COUNTIF(A1:A6,F1:F4)) gives the correct count of the DeliveryMethods. But I need to then combine this with the Delivery date

 

[FDibbins]

Something like this will work, but I have hard-coded the criteria...

	A​	B​	C​	D​
1​	aa	10​	aa	150​
2​	bb	20​	bb	180​
3​	cc	30​
4​	dd	40​		330​
5​	aa	50​
6​	bb	60​
7​	cc	70​
8​	dd	80​
9​	aa	90​
10​	bb	100​
11​	cc	110​
12​	dd	120​

D1 and D2 are just a regular sumifs()
=SUMIF($A$1:$A$12,C1,$B$1:$B$12)
copied down (to show the values)

D4=SUM(SUMIFS($B$1:$B$12,$A$1:$A$12,{"aa","bb"}))

 

[a8_tony]

Thanks Ford

I'm working from home today, so will have a bit more of a look into this

 

[FDibbins]

OK keep me posted