Counting recurrence based on 2 criterias

[rniculae]

Hello guys,

I am struggling with a formula, I cannot think of a way to create it in a way where it doesn't need any further manual intervention.
Tried with a pivot table but I can't seem to make GETPIVOTDATA to work.

Can you please help me out?

I have a database with Ref number/ week number / country / number

Table-1	A	B	C	D
1	Country	Ref Number	Week Number	#
2	UK	123	W26	12
3	Germany	445	W26	-12
4	UK	123	W27	12
5	Netherlands	555	W27	-12
6	UK	342	W28	12

<tbody>
</tbody>

So I can have multiple countries with multiple ref numbers and in different weeks and on column D I can have only 12 or -12.

Table-2	A	B	C	D
1	UK	W26	W27	W28
2	W26	1	1	0
3	W27		1	0
4	W28			1

<tbody>
</tbody>

The Idea is to get a count based on week number how many ref numbers are in a recurrence in the following weeks. (E.G. Table 1 Cell B2 is in week 26 and it has a recurrence in week 27 also but in week 28 ref number 123 doesn't show up anymore so that's why it is 0 in table 2 cell D2.

In table 2 Cell B2,C3,D4 shows how many ref numbers do we have new in that week.

Can I get a formula that applies to Table 2 based on Table 1 database considering that I will need the formula to expand to week 52 on rows and columns and everything is based on country level?

Right now I am doing a formula with countifs(Table1!A:A,Table2!A1,Table1!C:C,Table2!B1,Table1!C:C,Table2!,C1) This is the formula for table 2 cell C2 and for further weeks I need to keep adding ,Table2!,D1 // ,Table2!,E1 and so on.

Thank you.
Razvan

 

[rniculae]

Hey guys,

Any help with it? I kept trying to create this formula and I don't have any good results.

Thanks,
Razvan

 

[Peter_SSs]

See if this, copied across and down does what you want. If not, post more sample data where it fails and also give us the expected results again.

Excel Workbook
	A	B	C	D
1	Country	Ref Number	Week Number	#
2	UK	123	W26	12
3	Germany	445	W26	-12
4	UK	123	W27	12
5	Netherlands	555	W27	-12
6	UK	342	W28	12
7
8
9	UK	W26	W27	W28
10	W26	1	1	0
11	W27		1	0
12	W28			1
Count Recurrence

 

[rniculae]

Hello Peter,

Thank you for trying but it doesn't work for me.

I will put here the whole file. https://we.tl/t-rnrYO9A4Dx - wetransfer link

So, for example, I have in my file - sheet Categories cell C2 59 (this means that I had 59 items with +/- 12 in week 26 then out of those 59 items, in week 27 we have a remainder of 33 and so on.

The results are shown in Categories Sheet.
I would like from this formula to apply to all weeks without needing to add another column (as you see in my formulas).



I have hidden rows on Categories sheet to hide unfilled rows. For those I would want this formula to work and as soon as I add new data in DB sheet, this should be updated automatically.



Is it doable?

Thanks,
Razvan

 

[Peter_SSs]

A bit more clarification please. In 'Detailed' ..

1. Confirm that 12, -12 are blank are the only values that will appear in columns F onwards.

2. In your sample, no rows in F onwards contain both 12 and -12. Is that always the case? If there can be a mix of 12 and -12 on a single row, how does that affect the results?

3. In your sample, no rows in F onwards have a 'break' between the non-blank values. Is that always the case or could a single row look like this?
blank 12 12 blank blank 12 12 12 blank

 

[rniculae]

1. In my database I will always have only 12 or -12.
2. My results are not affected by 12 or -12, I just need the counting. This means that if it is easier for you you can use count instead of sum for the values so it will show up only "1" instead of 12 or -12.
3. The item that has the value of 12, it will be 12 until the recurrence stops. In a very unlikely event, after 12 weeks of blank, it can show up again as 12 or -12 ( this should be very very rare, maybe 1 in 52 weeks.) I can live without it.

Thank you!
Razvan

 

[Peter_SSs]

Thanks for the clarifications. Re point 3, if you do get the 12/-12 reappearing for a second sequence after some blanks, I suspect my formulas will fail - I haven't been able to test.
Otherwise, see if this helps.

To keep the formulas a bit simpler I have inserted a new row 1 in 'Categories' and populated it manually as shown. That row could then be hidden. We could do without this helper row but all the formulas would be a bit more complicated.

To enter all the required formulas you would no doubt have to unhide all the rows in 'Categories'

For the top section (Total) I have drawn the results from the lower section of 'Categories' rather than reverting back to 'Detailed'
The formula in C3 is copied and Paste Special (Formulas) to the other populated cells in that section.

For 'Cereal' the formula in C32 is copied and Paste Special (Formulas) to the other populated cells in that section.

Copy/Paste the C32 formula to C61 and then change the reference from $A$31 to $A$60. C61 can then be copied & Paste Special (Formulas) to the other relevant cells in the Snakcks1 section.

Repeat for other sections.

We could avoid having to do the blue part above but the formulas would all again become a bit more complicated.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			1	2	3	4	5	6	7	8	9
2	Total		W26	W27	W28	W29	W30	W31	W32	W33	W34
3		W26	59	33	23	15	11	9	6
4		W27		41	28	20	15	12	9
5		W28			37	28	18	14	11
6		W29				40	26	18	15
7		W30					36	27	20
8		W31						31	23
9		W32							38
30
31	Cereal		W26	W27	W28	W29	W30	W31	W32	W33	W34
32		W26	41	20	14	10	8	7	5
33		W27		25	18	14	11	10	8
34		W28			23	18	12	10	8
35		W29				24	17	13	11
36		W30					21	16	13
37		W31						19	15
38		W32							17
59
60	Snacks1		W26	W27	W28	W29	W30	W31	W32	W33	W34
61		W26	13	10	6	4	3	2	1
62		W27		12	7	5	4	2	1
63		W28			10	8	6	4	3
64		W29				11	7	4	3
65		W30					11	8	5
66		W31						8	5
67		W32							12
88
89	Cereal Bars		W26	W27	W28	W29	W30	W31	W32	W33	W34
90		W26	0	0	0	0	0	0	0
91		W27		1	0	0	0	0	0
92		W28			1	1	0	0	0
93		W29				1	0	0	0
94		W30					0	0	0
95		W31						0	0
96		W32							1
117
118	Snacks2		W26	W27	W28	W29	W30	W31	W32	W33	W34
119		W26	5	3	3	1	0	0	0
120		W27		3	3	1	0	0	0
121		W28			3	1	0	0	0
122		W29				4	2	1	1
123		W30					4	3	2
124		W31						4	3
125		W32							8
Categories

Cell Formulas
Range	Formula
C3	=SUMIF($B$31:$B$200,$B3,C$31:C$200)
C32	=SUMPRODUCT(--(Detailed!$A$3:$A$300=$A$31),--(INDEX(Detailed!$F$3:$AF$300,0,C$1-COUNT($B32:B32))<>""),--(INDEX(Detailed!$F$3:$AF$300,0,C$1)<>""))
C61	=SUMPRODUCT(--(Detailed!$A$3:$A$300=$A$60),--(INDEX(Detailed!$F$3:$AF$300,0,C$1-COUNT($B61:B61))<>""),--(INDEX(Detailed!$F$3:$AF$300,0,C$1)<>""))
C90	=SUMPRODUCT(--(Detailed!$A$3:$A$300=$A$89),--(INDEX(Detailed!$F$3:$AF$300,0,C$1-COUNT($B90:B90))<>""),--(INDEX(Detailed!$F$3:$AF$300,0,C$1)<>""))
C119	=SUMPRODUCT(--(Detailed!$A$3:$A$300=$A$118),--(INDEX(Detailed!$F$3:$AF$300,0,C$1-COUNT($B119:B119))<>""),--(INDEX(Detailed!$F$3:$AF$300,0,C$1)<>""))

 

[rniculae]

Hello Peter,

This worked flawlessly. Thank you very much! I really appreciate it!
Now I have to go on youtube to learn sumproduct basics.
Never used it but after I followed your formula it looks like it is a great function.

Thanks again!

Razvan

 

[Peter_SSs]

Cheers, glad it worked for you. It did take me quite a while to get my head around what was required.