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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]DeliveryMethod[/TD]
[TD]AllocatedQty[/TD]
[TD]PromisedDeliveryDate[/TD]
[TD]PickedQty[/TD]
[/TR]
[TR]
[TD]CARRIER[/TD]
[TD]10[/TD]
[TD]15/02/2017[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CARRIER[/TD]
[TD]5[/TD]
[TD]15/02/2017[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]DELIVERY - 03[/TD]
[TD]10[/TD]
[TD]15/02/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]DELIVERY - 04[/TD]
[TD]2[/TD]
[TD]15/02/2017[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]DELIVERY - 03[/TD]
[TD]3[/TD]
[TD]14/02/2017[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

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):

[TABLE="width: 500"]
<tbody>[TR]
[TD]DELIVERY - 03
[/TD]
[/TR]
[TR]
[TD]DELIVERY - 04[/TD]
[/TR]
[TR]
[TD]CARRIER[/TD]
[/TR]
[TR]
[TD]COLLECTION[/TD]
[/TR]
</tbody>[/TABLE]

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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]aa[/td][td]

10​

[/td][td]aa[/td][td]

150​

[/td][/tr]

[tr][td]

2​

[/td][td]bb[/td][td]

20​

[/td][td]bb[/td][td]

180​

[/td][/tr]

[tr][td]

3​

[/td][td]cc[/td][td]

30​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]dd[/td][td]

40​

[/td][td][/td][td]

330​

[/td][/tr]

[tr][td]

5​

[/td][td]aa[/td][td]

50​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]bb[/td][td]

60​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]cc[/td][td]

70​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]dd[/td][td]

80​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]aa[/td][td]

90​

[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]bb[/td][td]

100​

[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]cc[/td][td]

110​

[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]dd[/td][td]

120​

[/td][td][/td][td][/td][/tr]
[/table]
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