SUMIFS +vlookup and Month/Quarter to date

[Giancar]

Hi,

I have 2 tables in 2 sheets.

Sheet1

name	Margin MTD	Target MTD	Margin QTD	Target QTD
James
Mark
Frank
Jim
Russell
Conrad
Denis

<tbody>
</tbody>

Sheet2

Year	Quarter	Month	Day	Name	Margin	Target
2018	Q2	May	01-05-2018	James	10	20
2018	Q2	May	01-05-2018	Mark	20	20
2018	Q2	May	01-05-2018	Frank	25	15
2018	Q2	May	01-05-2018	Jim	31	30
2018	Q2	May	02-05-2018	James	23	22
2018	Q2	May	02-05-2018	Mark	4	18
2018	Q2	May	02-05-2018	Frank	100	66
2018	Q2	May	02-05-2018	Jim	32	41
2018	Q2	May	02-05-2018	Russell	11	40
2018	Q2	May	02-05-2018	Conrad	23	20
2018	Q2	May	03-05-2018	Mark		67
2018	Q2	May	03-05-2018	Denis		33
2018	Q2	June	05-06-2018	Mark		20
2018	Q2	June	05-06-2018	Frank		10
2018	Q2	June	05-06-2018	Jim		4
2018	Q2	June	05-06-2018	James		3

<tbody>
</tbody>

Result:
In B2 (Table Sheet1), I would like to have: sum column F (Table sheet2), <= Today date (dynamic), if column E (Table sheet2) is matching column A (Table sheet1) and it is the current month.
In C2 (table Sheet1), same per above, but sum of column G (Table sheet2)
In D2 (Table Sheet1), I would like to have: sum column F (Table sheet2), <= Today date (dynamic), if column E (Table sheet2) is matching column A (Table sheet1) and it is the current quarter or Q2.
In E2 (table Sheet1), same per above, but sum of column G (Table sheet2)

I tried to use helper column/cell but I am not able to find a solution by myself. Any help?

Thank you

 

[jtakw]

Hi,

I believe this is what you're looking for.
I have a question, what constitutes "Current Quarter"?, You might want to have a cell reference for this criteria, at the moment, it's hardcoded into formula D2:

Book1
	A	B	C	D	E
1	name	Margin MTD	Target MTD	Margin QTD	Target QTD
2	James	33	42	33	42
3	Mark	24	38	24	38
4	Frank	125	81	125	81
5	Jim	63	71	63	71
6	Russell	11	40	11	40
7	Conrad	23	20	23	20
8	Denis	0	0	0	0
Sheet 1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(('Sheet 2'!$E$2:$E$17=$A2)*('Sheet 2'!$D$2:$D$17<=TODAY())*(MONTH('Sheet 2'!$D$2:$D$17)=MONTH(TODAY()))*'Sheet 2'!F$2:F$17)
D2	=SUMPRODUCT(('Sheet 2'!$E$2:$E$17=$A2)*('Sheet 2'!$D$2:$D$17<=TODAY())*('Sheet 2'!$B$2:$B$17="Q2")*'Sheet 2'!F$2:F$17)

Book1
	A	B	C	D	E	F	G
1	Year	Quarter	Month	Day	Name	Margin	Target
2	2018	Q2	May	5/1/2018	James	10	20
3	2018	Q2	May	5/1/2018	Mark	20	20
4	2018	Q2	May	5/1/2018	Frank	25	15
5	2018	Q2	May	5/1/2018	Jim	31	30
6	2018	Q2	May	5/2/2018	James	23	22
7	2018	Q2	May	5/2/2018	Mark	4	18
8	2018	Q2	May	5/2/2018	Frank	100	66
9	2018	Q2	May	5/2/2018	Jim	32	41
10	2018	Q2	May	5/2/2018	Russell	11	40
11	2018	Q2	May	5/2/2018	Conrad	23	20
12	2018	Q2	May	5/3/2018	Mark		67
13	2018	Q2	May	5/3/2018	Denis		33
14	2018	Q2	June	6/5/2018	Mark		20
15	2018	Q2	June	6/5/2018	Frank		10
16	2018	Q2	June	6/5/2018	Jim		4
17	2018	Q2	June	6/5/2018	James		3
Sheet 2

B2 formula copied down and across to C8
D2 formula copied down and across to E8

 

[Giancar]

Hi,

That is exactly the result I want, but it is not working to me.
I give you a link with the file, maybe you can find the problem. I tried to add a helper column but nothing. I am stuck.
Thank you in advance.

https://www.dropbox.com/s/do0bdeeka91fzqa/table.xlsx?dl=0

 

[jtakw]

OK, the problem is, all your Dates in Column D of Sheet2 are not Real Dates.

There are a few ways to fix this:

1. Add a helper Column, and use one of the formulas below (L2, M2, N2) copy down, then adjust my Post#2 formulas' references to the New Column for Dates.
2. Just enter a 0 (that's a zero), like in L3, Copy this cell, Select/Highlight D2 all the way down to the last date, right click, select Paste Special, near the middle of pop up box "Operation", select "add", click OK.

Either way, format Column as Date the way you want:

Book1
	A	B	C	D	E	F	I	J	K	L	M	N
1	Year	Quarter	Month	Day	Division	Margin	Target
2	2018	Q2	May	2018-05-01	Abruzzo		£ 14.63		FALSE	5/1/2018	5/1/2018	5/1/2018
3	2018	Q2	May	2018-05-01	Aosta Valley		£ 29.26			0
4	2018	Q2	May	2018-05-01	Apulia	£17	£ 87.77
Sheet2

Cell Formulas
Range	Formula
K2	=ISNUMBER(D2)
L2	=DATEVALUE(D2)
M2	=D2+0
N2	=D2*1

If you can't or don't want to do any of the above to "fix" the dates, I can tweak my formulas to accommodate, let me know.

 

[Giancar]

Hi
Unofrtunately your formulas are not working either....I mean, they return the correct date, with date format, but then, after replacing the column formula reference, it gives me #VALUE !
Yes, you're right, there wasn't a day format, for that reason I tried to add previously helper column but in vain. I tried also the following formula =DATE(YEAR(D2),MONTH(D2),DAY(D2)).

Just to provide you more information. Table in sheet2 is a pivot table and I may need to refresh it time to time. So trick to add 0 can not be applied.

 

[jtakw]

Hi
Unofrtunately your formulas are not working either....I mean, they return the correct date, with date format, but then, after replacing the column formula reference, it gives me [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] !
Yes, you're right, there wasn't a day format, for that reason I tried to add previously helper column but in vain. I tried also the following formula =DATE(YEAR(D2),MONTH(D2),DAY(D2)).

Just to provide you more information. Table in sheet2 is a pivot table and I may need to refresh it time to time. So trick to add 0 can not be applied.

Please upload your spreadsheet after you've corrected the D Column dates, after replacing column formula reference, and getting #VALUE error...

 

[Giancar]

https://www.dropbox.com/s/do0bdeeka91fzqa/table.xlsx?dl=0

 

[jtakw]

The problem this time, is that you're using Entire Column references, which generally is Not recommended because it slows down calculations (there are Over 1 million rows in Excel).
In this case, since you have Headers in the Columns, it's causing the #VALUE error, change the formula to actual references, instead of 621 as I've used, you can go as high as you need/want, but start with row 2 because of your Headers:

Book1
	A	B
1	DIVISION	Margin MTD
2	Abruzzo	0
3	Aosta Valley	60.96228019
4	Apulia	180.6196783
5	Basilicate	63.44710185
6	Calabria	0
7	Campania	1026.474741
8	Naples	0
9	Ischia	0
10	Emilia-Romagna	159.8424131
11	Friuli Venezia Giulia	139.7346369
12	Latium	249.3130531
13	Rome	0
14	Liguria	115.8430544
15	Lombardy	46.71128423
16	Milan	0
17	Marches	0
18	Molise	0
19	Piedmont	172.7969762
20	Sardinia	707.4486851
21	Sicily	476.8710177
22	Trentino-Alto Adige	49.0104151
23	Tuscany	754.1989432
24	Florence	0
25	Umbria	601.3599439
26	Veneto	405.4970363
27	Venice	0
Sheet1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((Sheet2!E$2:E$621=$A2)*(Sheet2!K$2:K$621<=TODAY())*(MONTH(Sheet2!K$2:K$621)=MONTH(TODAY()))*Sheet2!F$2:F$621)

 

[Giancar]

Simply perfect! Thank you so much

 

[jtakw]

Wow, after 3 weeks, didn't know if you got it fixed.

You're welcome, glad it's working, thanks for the feedback.