Solved: How to formulate sumifs based on weeknum?

[genracela]

My formula is:


=IF(A3="ALL",SUMIFS(D8:D65000,C8:C65000,B3,K8:K65000,WEEKNUM(C3)),SUMIFS(D8:D65000,A8:A 65000,A3,C8:C65000,B3,K8:K65000,WEEKNUM(C3)))

I'm trying to come up with an answer based on weeknum, but with a condition that:

When C3 is less less than the dates in K8:K6500, it will only return the sum of those dates.

Example in the attached file:

C3 is 5-Jan-10, if you look at the filtered data below 05-Jan-10 only show 111,021, I want to return this numner in D3 instead of 634,560. However, if C3 is 6-Jan-10, D3 should show 220,159, which is the sum of G164+G181.

Please help:help


Thanks in advance!

[Bob Phillips]

You have lost me. All of the data you show is week 2, and both the 5th and 6th Jan are week 2 so those dates will not return a different value. Also you refer to a value of 11,021 which I can see, but then you refere to G164+G181, both of which are hidden, and a total of 220,159 which I cannot see with the rows showing.

[genracela]

Sorry, my bad, here are the conditions again:

If K8:K65000 = Weeknum(C3) and G8:G65000 <= C3, then return Sum(F8:F65000).

I hope it's clear, I don't really know how to explain it further. sorry.:dunno

[Bob Phillips]

Don't you just want



=IF(A3="ALL",SUMIFS(D8:D61275,C8:C61275,B3,K8:K61275,WEEKNUM(C3),G8:G61275,"<="&C3),
SUMIFS(D8:D61275,A8:A61275,A3,C8:C61275,B3,K8:K61275,WEEKNUM(C3),G8:G61275,"<="&C3))

[genracela]

Yes that's it!!!

Thank you very very veryyyyyyyyyyyyyy much!!!!Mwahhh!