Help/guidance needed on nested Sumif formula condition range

[MJ Erasmus]

Hi,

I am busy creating a "Live sheet" for the team to track progress in real-time.

this sheet is hosted on our SharePoint and thus eliminates the possibility to use VBA scripting which in all fairness would work best here.

I broke the link and added dummy data so that I can post it here.

currently, the summary works but I need to use additional rows to extract and refine data which I am trying to get rid of.

I tried searching online but for the life of me I cannot see if this is actually possible or not hence I am resorting to ask the experts here.

I am essentially trying to get the condition range of the formula broken down to either the month or the week number before it evaluates the rest of the statement.

=SUMIFS($C$4:$O$4,$C$2:$O$2,"="&NUMBERVALUE(RIGHT(D10,LEN(D10)-5)))

basically what I am trying to achieve and the simplest way to explane is to have it something like this. =SUMIFS($C$4:$O$4,weeknum($C$3:$O$3),"="&NUMBERVALUE(RIGHT(D10,LEN(D10)-5)))

Sales Specialist Tracker - Summary.2.xlsx
	B	C	D	E	F
1	Month	2	2	2	2
2	Week	5	5	5	6
3		01-Feb	02-Feb	03-Feb	06-Feb
4	Total Meetings	8	9		1
5	Total Quoted	1	5		1
6	Total Quoted MRC	R 1 698.84	R 20 000.00		R 500.00
7	Total MRC	R 1 698.84	R 30 000.00		R 5 000.00
8
9
10		Week 5	Week 6	Week 7	Week 8
11	Total Meetings	17	1	0	0
12	Total Quoted	6	1	0	0
13	Total Quoted MRC	R 21 698.84	R 500.00	R -	R -
14	Total MRC	R 31 698.84	R 5 000.00	R -	R -
15
16		Jan	Feb	Mar	Apr
17	Total Meetings	0	18	0	0
18	Total Quoted	0	7	0	0
19	Total Quoted MRC	R -	R 22 198.84	R -	R -
20	Total MRC	R -	R 36 698.84	R -	R -
Chart tracker (2)

Cell Formulas
Range	Formula
C1:F1	C1	=MONTH(C3)
C2:F2	C2	=WEEKNUM(C3)
C3:F3	C3	='Daily Tracker - Summary'!C1
B4:B7	B4	='Daily Tracker - Summary'!B34
C11:F11	C11	=SUMIFS($C$4:$O$4,$C$2:$O$2,"="&NUMBERVALUE(RIGHT(C10,LEN(C10)-5)))
C12:F12	C12	=SUMIFS($C$5:$O$5,$C$2:$O$2,"="&NUMBERVALUE(RIGHT(C10,LEN(C10)-5)))
C13:F13	C13	=SUMIFS($C$6:$O$6,$C$2:$O$2,"="&NUMBERVALUE(RIGHT(C10,LEN(C10)-5)))
C14:F14	C14	=SUMIFS($C$7:$O$7,$C$2:$O$2,"="&NUMBERVALUE(RIGHT(C10,LEN(C10)-5)))
C17:F17	C17	=SUMIFS($C$4:$O$4,$C$1:$O$1,"="&NUMBERVALUE(MONTH(C16)))
C18:F18	C18	=SUMIFS($C$5:$O$5,$C$1:$O$1,"="&NUMBERVALUE(MONTH(C16)))
C19:F19	C19	=SUMIFS($C$6:$O$6,$C$1:$O$1,"="&NUMBERVALUE(MONTH(C16)))
C20:F20	C20	=SUMIFS($C$7:$O$7,$C$1:$O$1,"="&NUMBERVALUE(MONTH(C16)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:O7	Expression	=C$3=TODAY()	text	NO
C4:O7	Expression	=WEEKNUM(C$3)=WEEKNUM(TODAY())	text	NO
C3:O3	Expression	=C$3=TODAY()	text	NO
C3:O3	Expression	=WEEKNUM(C$3)=WEEKNUM(TODAY())	text	NO

 

[Fluff]

How about

=SUMPRODUCT((WEEKNUM(--C3:O3)=--(RIGHT(C10,2)))*(C4:O4))

 

[MJ Erasmus]

How about

=SUMPRODUCT((WEEKNUM(--C3:O3)=--(RIGHT(C10,2)))*(C4:O4))

works perfectly thank you

 

[Fluff]

You're welcome & thanks for the feedback.