Count then subtract with matching month & date >= today

[ShalDRH]

Hello Excel wizards! I have attempted about a dozen formulas to get the outcome I need and I fall short each time! I'm certain this will be a piece of cake for you!

I have tried to paste in the "mini sheet" to make it easy to help. Fingers Crossed.

There are two tabs- "Metrics Review" tab lists activities horizontally across a time span (with date name titles I can't change as they are consistent with a larger data in the spreadsheet) and "Milestones" tab which is a linked data set from a power pivot that I need to use in my formula.

Here is the calculation I need help with-

On "Milestones" Count the # of occurrences from the pivot given the project #, month, and year. On the "Metrics Review" Subtract the recorded closings for the matching project/month/year from that Milestones count, but return zero if the month is in the past, and return zero if the month is both in the future AND not represented on the pivot.

So for example

1. September 2021 there are 12 occurrences on the pivot and subtracting the 14 closings returns the value of 14.
2. April 2022 there are no occurrences on the pivot so it should just stop and be zero without trying to subtract the seven closings.

Thanks for your help!

Power Pivot tab "Milestones"

count help 092121.xlsm
	A	B	C
3	Project2	Month	Year
4	56058	9	2021
5	56058	9	2021
6	56058	9	2021
7	56058	9	2021
8	56058	10	2021
9	56058	10	2021
10	56058	10	2021
11	56058	11	2021
12	56058	10	2021
13	56058	10	2021
14	56058	11	2021
15	56058	11	2021
16	56058	10	2021
17	56058	2	2022
18	56058	2	2022
19	56058	2	2022
20	56058	3	2022
21	56058	2	2022
22	56058	2	2022
23	56058	2	2022
24	56058	2	2022
25	56058	2	2022
26	56058	2	2022
27	56058	2	2022
28	56058	1	2022
29	56058	1	2022
30	56058	1	2022
31	56058	12	2021
32	56058	11	2021
33	56058	11	2021
34	56058	11	2021
35	56058	12	2021
36	56058	11	2021
37	56058	11	2021
38	56058	11	2021
39	56058	9	2021
40	56058	9	2021
41	56058	9	2021
42	56058	9	2021
43	56058	9	2021
44	56058	9	2021
45	56058	9	2021
46	56058	9	2021
47	56058	1	1900
48	56058	1	1900
Milestones

Timeline tab "Metrics Review"

count help 092121.xlsm
	A	B								J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
4										Q3			Q4			Q1			Q2			Q3			Q4
5	Co#	Data								APR 2021	MAY 2021	JUN 2021	JUL 2021	AUG 2021	SEP 2021	OCT 2021	NOV 2021	DEC 2021	JAN 2022	FEB 2022	MAR 2022	APR 2022	MAY 2022	JUN 2022	JUL 2022	AUG 2022	SEP 2022
6	56058	Closings								-	-	-	3	2	14	7	7	8	8	8	7	7	7	7	8	8	8
7
8
9	56058	Desired Outcome								0	0	0	0	0	(2)	(1)	2	(6)	(5)	2	(6)	0	0	0	0	0	0
Metrics Review

Cell Formulas
Range	Formula
O9	O9	=12-O6
P9	P9	=6-P6
Q9	Q9	=9-Q6
R9	R9	=2-R6
S9	S9	=3-S6
T9	T9	=10-T6
U9	U9	=1-U6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:O8	Cell Value	<0	text	NO

 

[ShalDRH]

Clarification- (I don't know how to edit my original post!)

So for example

1. September 2021 there are 12 occurrences on the pivot and subtracting the 14 closings returns the value of negative 2 (not the 14 I typo'd!)

 

[Alex Blakenburg]

See if this works for you.

Your profile says you have 365, so if you are happy to use the LET function the row 11 version should be easier to read.
I have spread the formula over multiple lines using Alt+Enter so you will most like have to hit the drop down arrow on the formula bar to see the whole formula.

20210921 Countifs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3
4										Q3			Q4			Q1			Q2			Q3			Q4
5	Co#	Data								Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
6	56058	Closings								0	0	0	3	2	14	7	7	8	8	8	7	7	7	7	8	8	8
7
8
9	56058	Calculation								0	0	0	0	0	-2	-1	2	-6	-5	2	-6	0	0	0	0	0	0
10
11	56058	Calculation - Use LET								0	0	0	0	0	-2	-1	2	-6	-5	2	-6	0	0	0	0	0	0
Metrics Review

Cell Formulas
Range	Formula
J9:AA9	J9	=IF(COUNTIFS(Milestones!$A$2:$A$46,$A9,Milestones!$C$2:$C$46,YEAR(J$5),Milestones!$B$2:$B$46,MONTH(J$5))=0, 0, -J$6+COUNTIFS(Milestones!$A$2:$A$46,$A9,Milestones!$C$2:$C$46,YEAR(J$5),Milestones!$B$2:$B$46,MONTH(J$5)))
J11:AA11	J11	=LET(metrics_cnt,COUNTIFS(Milestones!$A$2:$A$46,$A11,Milestones!$C$2:$C$46,YEAR(J$5),Milestones!$B$2:$B$46,MONTH(J$5)), IF(metrics_cnt=0,0,-J$6+metrics_cnt))

 

[ShalDRH]

See if this works for you.

Your profile says you have 365, so if you are happy to use the LET function the row 11 version should be easier to read.
I have spread the formula over multiple lines using Alt+Enter so you will most like have to hit the drop down arrow on the formula bar to see the whole formula.

20210921 Countifs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3
4										Q3			Q4			Q1			Q2			Q3			Q4
5	Co#	Data								Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
6	56058	Closings								0	0	0	3	2	14	7	7	8	8	8	7	7	7	7	8	8	8
7
8
9	56058	Calculation								0	0	0	0	0	-2	-1	2	-6	-5	2	-6	0	0	0	0	0	0
10
11	56058	Calculation - Use LET								0	0	0	0	0	-2	-1	2	-6	-5	2	-6	0	0	0	0	0	0
Metrics Review

Cell Formulas
Range	Formula
J9:AA9	J9	=IF(COUNTIFS(Milestones!$A$2:$A$46,$A9,Milestones!$C$2:$C$46,YEAR(J$5),Milestones!$B$2:$B$46,MONTH(J$5))=0, 0, -J$6+COUNTIFS(Milestones!$A$2:$A$46,$A9,Milestones!$C$2:$C$46,YEAR(J$5),Milestones!$B$2:$B$46,MONTH(J$5)))
J11:AA11	J11	=LET(metrics_cnt,COUNTIFS(Milestones!$A$2:$A$46,$A11,Milestones!$C$2:$C$46,YEAR(J$5),Milestones!$B$2:$B$46,MONTH(J$5)), IF(metrics_cnt=0,0,-J$6+metrics_cnt))

Thanks Alex! This worked perfectly for me and makes total sense for how you arranged the formula. I'll be able to use the same logic path on similar issues where I was approaching it somewhat backward. I greatly appreciate your help. Have a wonderful day!

Shalon

 

[Alex Blakenburg]

Great feedback, thank you. Always nice to know that someone has walked away with more than just an answer to the immediate problem.