SUMIFS +vlookup and Month/Quarter to date

G

Giancar

Board Regular
Joined
Nov 29, 2017
Messages
52
Hi,

I have 2 tables in 2 sheets.

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]Margin MTD[/TD]
[TD]Target MTD[/TD]
[TD]Margin QTD[/TD]
[TD]Target QTD[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Russell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Conrad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Denis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[TD]Name[/TD]
[TD]Margin[/TD]
[TD]Target[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]James[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]Mark[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]Frank[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]01-05-2018[/TD]
[TD]Jim[/TD]
[TD]31[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]James[/TD]
[TD]23[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Mark[/TD]
[TD]4[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Frank[/TD]
[TD]100[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Jim[/TD]
[TD]32[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Russell[/TD]
[TD]11[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]02-05-2018[/TD]
[TD]Conrad[/TD]
[TD]23[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]03-05-2018[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]May[/TD]
[TD]03-05-2018[/TD]
[TD]Denis[/TD]
[TD][/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Frank[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]Jim[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Q2[/TD]
[TD]June[/TD]
[TD]05-06-2018[/TD]
[TD]James[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
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
ABCDE
1nameMargin MTDTarget MTDMargin QTDTarget QTD
2James33423342
3Mark24382438
4Frank1258112581
5Jim63716371
6Russell11401140
7Conrad23202320
8Denis0000
Sheet 1
Cell Formulas
RangeFormula
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
ABCDEFG
1YearQuarterMonthDayNameMarginTarget
22018Q2May5/1/2018James1020
32018Q2May5/1/2018Mark2020
42018Q2May5/1/2018Frank2515
52018Q2May5/1/2018Jim3130
62018Q2May5/2/2018James2322
72018Q2May5/2/2018Mark418
82018Q2May5/2/2018Frank10066
92018Q2May5/2/2018Jim3241
102018Q2May5/2/2018Russell1140
112018Q2May5/2/2018Conrad2320
122018Q2May5/3/2018Mark67
132018Q2May5/3/2018Denis33
142018Q2June6/5/2018Mark20
152018Q2June6/5/2018Frank10
162018Q2June6/5/2018Jim4
172018Q2June6/5/2018James3
Sheet 2


B2 formula copied down and across to C8
D2 formula copied down and across to E8
 
Upvote 0
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
ABCDEFIJKLMN
1YearQuarterMonthDayDivisionMarginTarget
22018Q2May2018-05-01Abruzzo£ 14.63FALSE5/1/20185/1/20185/1/2018
32018Q2May2018-05-01Aosta Valley£ 29.260
42018Q2May2018-05-01Apulia£17£ 87.77
Sheet2
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
Giancar said:
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.
Click to expand...

Please upload your spreadsheet after you've corrected the D Column dates, after replacing column formula reference, and getting #VALUE error...
 
Upvote 0
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
AB
1DIVISIONMargin MTD
2Abruzzo0
3Aosta Valley60.96228019
4Apulia180.6196783
5Basilicate63.44710185
6Calabria0
7Campania1026.474741
8Naples0
9Ischia0
10Emilia-Romagna159.8424131
11Friuli Venezia Giulia139.7346369
12Latium249.3130531
13Rome0
14Liguria115.8430544
15Lombardy46.71128423
16Milan0
17Marches0
18Molise0
19Piedmont172.7969762
20Sardinia707.4486851
21Sicily476.8710177
22Trentino-Alto Adige49.0104151
23Tuscany754.1989432
24Florence0
25Umbria601.3599439
26Veneto405.4970363
27Venice0
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
Wow, after 3 weeks, didn't know if you got it fixed.

You're welcome, glad it's working, thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
My Countif formula is not working.
Replies
4
Views
634
Fluff
Fluff
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
A
  • Question Question
VBA Read dates
2
Replies
11
Views
1K
Andyb2
A
LRATOZ
  • Solved
Formula needed to find highest total for a given month over the years
2
Replies
10
Views
2K
Peter_SSs
Peter_SSs
P
  • Question Question
Sum range totals in a date range where range does not match criteria
Replies
2
Views
819
Pepperoni
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top