Return sum of accounting dates based upon a match of corresponding cells

[sambusby]

Hi,

I am looking for a formula route to return a count of accounting dates based upon a match of corresponding cells. I’ve had a look through the forum but can’t see a similar answer, although I have seen a few with more complex analysis.

The Reference data is no more than 100 lines. Sadly reference data is locked, and therefore cannot be re-ordered and filtered prior - to allow a range helper.

Any help greatly appreciated.

Looking for multiple criteria formula in place of the red cells (Col F2 down) where it find the search value in the item column (Col A) and list the number of unique corresponding accounting dates (Col C) it finds in the accounting date field.

Thanks
Sam

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="class: outer_border, width: 882"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Item
[/TD]
[TD]Description
[/TD]
[TD]Accounting Date
[/TD]
[TD][/TD]
[TD]Search Value
[/TD]
[TD]How many instances of Different Accounting Dates per item?
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2W
[/TD]
[TD]Item 1
[/TD]
[TD]01/09/2017
[/TD]
[TD][/TD]
[TD]2W
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]9M
[/TD]
[TD]Item 3
[/TD]
[TD]01/09/2017
[/TD]
[TD][/TD]
[TD]3D
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3D
[/TD]
[TD]Item 2
[/TD]
[TD]02/09/2017
[/TD]
[TD][/TD]
[TD]4F
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3D
[/TD]
[TD]Item 2
[/TD]
[TD]03/09/2017
[/TD]
[TD][/TD]
[TD]7J
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]4F
[/TD]
[TD]Item 8
[/TD]
[TD]01/09/2017
[/TD]
[TD][/TD]
[TD]8X
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4F
[/TD]
[TD]Item 8
[/TD]
[TD]02/09/2017
[/TD]
[TD][/TD]
[TD]9M
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]4F
[/TD]
[TD]Item 8
[/TD]
[TD]03/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]4F
[/TD]
[TD]Item 8
[/TD]
[TD]03/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]9M
[/TD]
[TD]Item 3
[/TD]
[TD]02/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]9M
[/TD]
[TD]Item 3
[/TD]
[TD]02/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]02/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]7J
[/TD]
[TD]Item 4
[/TD]
[TD]05/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]8X
[/TD]
[TD]Item 5
[/TD]
[TD]01/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]8X
[/TD]
[TD]Item 5
[/TD]
[TD]01/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]9M
[/TD]
[TD]Item 3
[/TD]
[TD]06/09/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...


=SUM(IF(FREQUENCY(IF($A$2:$A$21=E2,$C$2:$C$21),$C$2:$C$21),1)) Control Shift Enter

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E	F
1	Item	Description	Accounting Date		Search Value	How many instances of Different Accounting Dates per item?
2	2W	Item 1	01/09/2017		2W	1
3	9M	Item 3	01/09/2017		3D	2
4	3D	Item 2	02/09/2017		4F	3
5	3D	Item 2	03/09/2017		7J	2
6	4F	Item 8	01/09/2017		8X	1
7	4F	Item 8	02/09/2017		9M	3
8	4F	Item 8	03/09/2017
9	4F	Item 8	03/09/2017
10	9M	Item 3	02/09/2017
11	9M	Item 3	02/09/2017
12	7J	Item 4	02/09/2017
13	7J	Item 4	05/09/2017
14	7J	Item 4	05/09/2017
15	7J	Item 4	05/09/2017
16	7J	Item 4	05/09/2017
17	7J	Item 4	05/09/2017
18	7J	Item 4	05/09/2017
19	8X	Item 5	01/09/2017
20	8X	Item 5	01/09/2017
21	9M	Item 3	06/09/2017
199

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($A$2:$A$21=$E2,$C$2:$C$21),$C$2:$C$21),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sambusby]

Exceptional. I've been looking at this for several hours. Thanks very much!