Count and sum all, or according to weeknumber and year, or year only if specified

[rovius]

I have sheets with this data (simplified for the example):
range A1:A10 = dates
range B1:B10 = items to count
range C1:C10 = items to sum

and I have two reference cells:
D1 = week number
D2 = year

I have used
one formula to count:
1) to count the whole column B range if no week number is specified in D1 (D1 is blank)
2) and to count the column B data for a specified week number if a week number is present in D1
=IF(ISBLANK(D1);COUNTA(B1:B10);SUMPRODUCT(--(WEEKNUM(A1:A10+0;2)=D1)+0;--(B1:B10<>"")))

and another formula to sum:
1) to sum the whole column C range if no week number is specified in D1 (D1 is blank)
2) and to sum the column C data for a specified week number if a week number is present in D1
=IF(ISBLANK(D1);SUM(C1:C10);SUMPRODUCT(--(WEEKNUM(A1:A10+0;2)=D1)+0;C1:C10))

Now my data has accumulated over one year, and I need to modify my two formulas so that they would:
1) count and sum according to week number and year, if specified in D1 and D2
2) count and sum according to year only, if specified in D2 (and D1 is blank)
3) count and sum the whole range, if no week number and year are specified (D1 and D2 are blank)
and still not using helper columns

Thanks in advance for all replies!

 

[DanteAmor]

Try this, (I hope to find a shorter formula ...)

Book1
	A	B	C	D	E	F
1	01/01/2019	1	1		Count	Sum
2	02/01/2019	0	2	2019	8	37
3	03/01/2019	1	3
4	04/01/2019	0	4
5	05/01/2019	1	5
6	06/01/2019	1	6
7	07/01/2019	2	7
8	08/01/2019
9	09/01/2019	2	9
10	10/01/2020	2	10
Sheet

Cell Formulas
Range	Formula
E2	E2	=IF(D1="",IF(D2="",COUNTA(B1:B10),SUMPRODUCT((B1:B10<>"")*(YEAR(A1:A10+0)=D2))), IF(D2="",SUMPRODUCT((B1:B10<>"")*(WEEKNUM(A1:A10+0,2)=D1)), SUMPRODUCT((B1:B10<>"")*(WEEKNUM(A1:A10+0,2)=D1)*(YEAR(A1:A10+0)=D2))))
F2	F2	=IF(D1="",IF(D2="",SUM(C1:C10),SUMPRODUCT((C1:C10<>"")*(YEAR(A1:A10+0)=D2)*(C1:C10))), IF(D2="",SUMPRODUCT((C1:C10<>"")*(WEEKNUM(A1:A10+0,2)=D1)), SUMPRODUCT((C1:C10<>"")*(WEEKNUM(A1:A10+0,2)=D1)*(YEAR(A1:A10+0)=D2)*(C1:C10))))

 

[DanteAmor]

Another options, but array formulas

{=SUM((B1:B10<>"")*(IF(D2="",1,YEAR(A1:A10)=D2))*(IF(D1="",1,WEEKNUM(A1:A10+0,2)=D1)))}

{=SUM((C1:C10<>"")*(IF(D2="",1,YEAR(A1:A10)=D2))*(IF(D1="",1,WEEKNUM(A1:A10+0,2)=D1))*(C1:C10))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

 

[rovius]

Thank you so much DanteAmor, I'm really grateful!
Both the regular and array formulas are working perfectly!
I will use the array formula which is impressively short and powerfull

 

[DanteAmor]

Hi @rovius, by the way, welcome to the board!

I'm glad to help you. Thanks for the feedback.