SUMIF (This week without helper column)

[jack109]

Hi, I've tried searching but all the info I can find uses a helper column. Is there a way to use SUMIF or SUMIFS to return the sum of data from the current week (Week day starting Monday) without adding a helper column.

I've tried =SUMIF(D:D,WEEKNUM(TODAY(),2),AA:AA)


Any ideas? Im sure it simple but everything just returns zero.

Cheers

 

[Fluff]

What is in col D?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jack109]

Column D is a list of dates in DD/MM/YYYY format.

I'll try and see if I can get the addin to work and post some data

 

[jack109]

TIPSTER TRACKER.xlsx
	C	D	E	F	G
3	DATE	PROFIT		P/L THIS WEEK	0
4	25/10/21	£14.00
5	25/10/21	£16.50
6	25/10/21	-£20.00
7	28/10/21	-£35.00
8	29/10/21	-£20.00
9	30/10/21	£16.50
10	31/10/21	£21.20
11	01/11/21	£15.00
12	01/11/21	£8.30
13	03/11/21	-£10.00
14	04/11/21	£23.75
15	04/11/21	£13.50
16	06/11/21	£8.10
17	08/11/21	-£20.00
18	08/11/21	-£20.00
Sheet1

Cell Formulas
Range	Formula
G3	G3	=SUMIF(C:C,WEEKNUM(TODAY(),2),D:D)

I've streamlined my data for this, I'm sure if somebody can provide an answer, I'd can adapt it to work on my sheet.

 

[Fluff]

Ok, how about

=SUMIFS(D:D,C:C,">="&WORKDAY.INTL(TODAY()-1,1,"0111111"),C:C,"<"&WORKDAY.INTL(TODAY()-1,2,"0111111"))

 

[jack109]

Excellent, seems to have done the job. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.