Help on Formula using 'WEEKNUMBER'

ovelzone

Hi all, Hope everyone is doing well? Hoping someone could help on using formula on ‘WEEKNUMBER’ from my below table (this is just a few transactions for an example) but the original table consists of large volume of transactions, I wanted to count totals of transactions on weekly basis from this table (eventually on to a different sheet). Tried using ‘WEEKNUMBER’ formula but cannot get it work.

Much appreciate if someone here could help me on this if there is a way? Many Thanks in advance!

I used below formula but i keep getting result as ‘0’

=SUMIF([Sold Date]:[Sold Date], WEEKNUMBER(TODAY() = "2", [Transaction

Total]:[Transaction Total]))

Nick Korna

The #INVALID DATA TYPE would suggest your date column isn't set as a date. Click on the header and change it over and things should improve!

jmyzk_cloudsmart_jp

Hi @ovelzone

If you want this week's "Transaction Total", try this;

=SUMIF([Sold Date]:[Sold Date], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Transaction Total]:[Transaction Total])

The condition "WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())" means that if one of the values in the [Sold Date]:[Sold Date] range has the same Week Number as that of today, add the Transaction Total value of the same row.

ovelzone

Hi, @jmyzk_cloudsmart_jp Thanks you so much for getting back on this query, i tried your formula but i keep getting results as below;

=SUMIF([Sold Date]:[Sold Date], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Transaction Total]:[Transaction Total]) The result for this formula: #INVALID DATA TYPE

I tried removing the @Cell in the formula to see what happens? and for this the result is as 0

=SUMIF([Sold Date]:[Sold Date], WEEKNUMBER() = WEEKNUMBER(TODAY()), [Transaction Total]:[Transaction Total])

Can't think of anything else?

Nick Korna

The #INVALID DATA TYPE would suggest your date column isn't set as a date. Click on the header and change it over and things should improve!

ovelzone

Hi, @Nick Korna Oh my god! it's so simple i didn't even realise that and keep kicking my self whole weekend :-) i just changed the column type to Date column and it's all working now. Thanks you so much for sorting it out. Have a good day!

ovelzone

@jmyzk_cloudsmart_jp and @Nick Korna Thank you both for sorting it out, it's all working as it supposed to. :-) Have a good day!

jmyzk_cloudsmart_jp

Happy to help!😁