I have a time tracker and trying to count time spent in a given week using SUMIFS.
The week column is a formula that gets the week number from the date, and not a value by itself, and that seems to be tripping up the SUMIFS calculation, and it just returns SUM of zero. If I replace the formula with a written week number, it works.
Is there a way to force SUMIFS to work with the formula, or make it see the value of the cell instead of the formula?
A B C D
wk48 johnny 29-11-18 misc 10
wk48 johnny 29-11-18 inbound 130
wk48 johnny 29-11-18 meeting 30
Column A is =WEEKNUM(C,2).
My SUMIFS formula is =SUMIFS(D:D,A:A,"wk48"), but like I said, it only returns zero. If I change the data in column A to be just text instead of getting the week number by formula, it works just fine.
(I'm using SUMIFS instead of just SUMIF because I want to add other criteria as well, but for now I'm trying to get the individual criteria working before combining them together, and I'm stuck on this one.)
Any ideas? Thanks.
the WEEKNUM formula returns numbers, 48 in your case, and not the string "wk48". I suspect this is how the cell is formatted (the same way you can have a dollar sign in front of a number). Try =SUMIFS(D:D,A:A,48).