Count then subtract with matching month & date >= today

ShalDRH

New Member
Joined
Jul 28, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
ABC
3Project2MonthYear
45605892021
55605892021
65605892021
75605892021
856058102021
956058102021
1056058102021
1156058112021
1256058102021
1356058102021
1456058112021
1556058112021
1656058102021
175605822022
185605822022
195605822022
205605832022
215605822022
225605822022
235605822022
245605822022
255605822022
265605822022
275605822022
285605812022
295605812022
305605812022
3156058122021
3256058112021
3356058112021
3456058112021
3556058122021
3656058112021
3756058112021
3856058112021
395605892021
405605892021
415605892021
425605892021
435605892021
445605892021
455605892021
465605892021
475605811900
485605811900
Milestones


Timeline tab "Metrics Review"
count help 092121.xlsm
ABJKLMNOPQRSTUVWXYZAA
4Q3Q4Q1Q2Q3Q4
5Co#DataAPR 2021MAY 2021JUN 2021JUL 2021AUG 2021SEP 2021OCT 2021NOV 2021DEC 2021JAN 2022FEB 2022MAR 2022APR 2022MAY 2022JUN 2022JUL 2022AUG 2022SEP 2022
656058Closings---3214778887777888
7
8
956058Desired Outcome00000(2)(1)2(6)(5)2(6)000000
Metrics Review
Cell Formulas
RangeFormula
O9O9=12-O6
P9P9=6-P6
Q9Q9=9-Q6
R9R9=2-R6
S9S9=3-S6
T9T9=10-T6
U9U9=1-U6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D6:O8Cell Value<0textNO
 

Attachments

  • 1632268125700.png
    1632268125700.png
    17.1 KB · Views: 6
  • 1632268135970.png
    1632268135970.png
    6.3 KB · Views: 6

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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!)
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2
3
4Q3Q4Q1Q2Q3Q4
5Co#DataApr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22
656058Closings0003214778887777888
7
8
956058Calculation00000-2-12-6-52-6000000
10
1156058Calculation - Use LET00000-2-12-6-52-6000000
Metrics Review
Cell Formulas
RangeFormula
J9:AA9J9=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:AA11J11=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))
 
Upvote 0
Solution
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2
3
4Q3Q4Q1Q2Q3Q4
5Co#DataApr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22
656058Closings0003214778887777888
7
8
956058Calculation00000-2-12-6-52-6000000
10
1156058Calculation - Use LET00000-2-12-6-52-6000000
Metrics Review
Cell Formulas
RangeFormula
J9:AA9J9=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:AA11J11=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
 
Upvote 0
Great feedback, thank you. Always nice to know that someone has walked away with more than just an answer to the immediate problem.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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