Completions % by Month and Current Year

F

francoiscj1

New Member
Joined
Aug 21, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
This is my original formula for completion %:

=COUNTIF($I13:$I101,"Compliant")/COUNTA($I13:$I101)

I would like to modify the formula to get completions % by month and current year. This is what I came up with:

=SUMPRODUCT((($F$13:$F$101)>=DATE(YEAR(TODAY()),1,1))*(($G$13:$G$101)>=DATE(YEAR(TODAY()),1,1))*(($H$13:$H$101)<=DATE(YEAR(TODAY()),1,31))*(($I$13:$I$101)="Compliant"))/COUNTA($I$13:$I$101)

Is my formula correct or can it be shortened?

Thanks for the help.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Can you try to explain what your objective is? Are you wanting to exclude certain values from the count? A mini worksheet and an expected result would be helpful.
But, it seems that you want to count records where two dates occur in 2024 (Today's year) and Another date Before Jan 31 of this year (Today's year) and are marked with a value of "Compliant" and then divide by the number of records where a compliance score that is not blank is populated? Is that right?
 
Upvote 0
what avlaue are supposed to be in the average set?
 
Upvote 0
Okay, I tested it out with smaller columns. So the formula is a bit different.
But, this would be how yours would be updated:

=SUMPRODUCT((YEAR(F13:F101)>=YEAR(TODAY()))*(YEAR(G13:G101)>=YEAR(TODAY()))*(H13:H101<=EOMONTH(G2,-(MONTH(TODAY())-1)))*(I13:I101="Compliant"))

Book1
ABCDEFGHIJKLM
4Date1Date2Date3Compliant
52024-10-272024-10-272024-05-12CompliantTRUETRUEFALSE2
62024-09-282024-09-282024-01-13Non-compliantTRUETRUETRUE
72024-09-012024-09-012024-01-14CompliantTRUETRUETRUE
82024-09-022024-09-022024-01-15CompliantTRUETRUETRUE
92024-09-032024-09-032024-09-16CompliantTRUETRUEFALSE
102024-09-042023-03-132024-09-17CompliantTRUEFALSEFALSE
112024-09-052023-03-142024-09-18CompliantTRUEFALSEFALSE
122024-09-062023-03-152024-09-19Non-compliantTRUEFALSEFALSE
132024-09-072023-03-162024-09-20CompliantTRUEFALSEFALSE
142024-09-302023-03-172024-09-21CompliantTRUEFALSEFALSE
152023-01-292023-03-182024-09-22Non-compliantFALSEFALSEFALSE
162023-03-162023-03-192024-09-23Non-compliantFALSEFALSEFALSE
172023-05-122023-07-122024-09-24CompliantFALSEFALSEFALSE
182023-11-102023-04-172024-09-25CompliantFALSEFALSEFALSE
192024-03-112024-10-192024-09-26CompliantTRUETRUEFALSE
202024-12-242024-04-032024-09-27Non-compliantTRUETRUEFALSE
212023-03-152023-11-212024-09-28CompliantFALSEFALSEFALSE
222024-11-162023-09-262024-09-29CompliantTRUEFALSEFALSE
232023-11-302024-10-252024-09-30Non-compliantFALSETRUEFALSE
242024-04-142024-02-122024-10-01CompliantTRUETRUEFALSE
Sheet3
Cell Formulas
RangeFormula
J5:K24J5=YEAR(F5:F24)>=YEAR($G$2)
L5:L24L5=H5:H24<=EOMONTH(G2,-(MONTH(G2)-1))
M5M5=SUMPRODUCT((YEAR(F5:F24)>=YEAR($G$2))*(YEAR(G5:G24)>=YEAR($G$2))*(H5:H24<=EOMONTH(G2,-(MONTH(G2)-1)))*(I5:I24="Compliant"))
Dynamic array formulas.
 
Upvote 0
Solution
awoohaw said:
Okay, I tested it out with smaller columns. So the formula is a bit different.
But, this would be how yours would be updated:

=SUMPRODUCT((YEAR(F13:F101)>=YEAR(TODAY()))*(YEAR(G13:G101)>=YEAR(TODAY()))*(H13:H101<=EOMONTH(G2,-(MONTH(TODAY())-1)))*(I13:I101="Compliant"))

Book1
ABCDEFGHIJKLM
4Date1Date2Date3Compliant
52024-10-272024-10-272024-05-12CompliantTRUETRUEFALSE2
62024-09-282024-09-282024-01-13Non-compliantTRUETRUETRUE
72024-09-012024-09-012024-01-14CompliantTRUETRUETRUE
82024-09-022024-09-022024-01-15CompliantTRUETRUETRUE
92024-09-032024-09-032024-09-16CompliantTRUETRUEFALSE
102024-09-042023-03-132024-09-17CompliantTRUEFALSEFALSE
112024-09-052023-03-142024-09-18CompliantTRUEFALSEFALSE
122024-09-062023-03-152024-09-19Non-compliantTRUEFALSEFALSE
132024-09-072023-03-162024-09-20CompliantTRUEFALSEFALSE
142024-09-302023-03-172024-09-21CompliantTRUEFALSEFALSE
152023-01-292023-03-182024-09-22Non-compliantFALSEFALSEFALSE
162023-03-162023-03-192024-09-23Non-compliantFALSEFALSEFALSE
172023-05-122023-07-122024-09-24CompliantFALSEFALSEFALSE
182023-11-102023-04-172024-09-25CompliantFALSEFALSEFALSE
192024-03-112024-10-192024-09-26CompliantTRUETRUEFALSE
202024-12-242024-04-032024-09-27Non-compliantTRUETRUEFALSE
212023-03-152023-11-212024-09-28CompliantFALSEFALSEFALSE
222024-11-162023-09-262024-09-29CompliantTRUEFALSEFALSE
232023-11-302024-10-252024-09-30Non-compliantFALSETRUEFALSE
242024-04-142024-02-122024-10-01CompliantTRUETRUEFALSE
Sheet3
Cell Formulas
RangeFormula
J5:K24J5=YEAR(F5:F24)>=YEAR($G$2)
L5:L24L5=H5:H24<=EOMONTH(G2,-(MONTH(G2)-1))
M5M5=SUMPRODUCT((YEAR(F5:F24)>=YEAR($G$2))*(YEAR(G5:G24)>=YEAR($G$2))*(H5:H24<=EOMONTH(G2,-(MONTH(G2)-1)))*(I5:I24="Compliant"))
Dynamic array formulas.
Click to expand...
awoohaw said:
Okay, I tested it out with smaller columns. So the formula is a bit different.
But, this would be how yours would be updated:

=SUMPRODUCT((YEAR(F13:F101)>=YEAR(TODAY()))*(YEAR(G13:G101)>=YEAR(TODAY()))*(H13:H101<=EOMONTH(G2,-(MONTH(TODAY())-1)))*(I13:I101="Compliant"))

Book1
ABCDEFGHIJKLM
4Date1Date2Date3Compliant
52024-10-272024-10-272024-05-12CompliantTRUETRUEFALSE2
62024-09-282024-09-282024-01-13Non-compliantTRUETRUETRUE
72024-09-012024-09-012024-01-14CompliantTRUETRUETRUE
82024-09-022024-09-022024-01-15CompliantTRUETRUETRUE
92024-09-032024-09-032024-09-16CompliantTRUETRUEFALSE
102024-09-042023-03-132024-09-17CompliantTRUEFALSEFALSE
112024-09-052023-03-142024-09-18CompliantTRUEFALSEFALSE
122024-09-062023-03-152024-09-19Non-compliantTRUEFALSEFALSE
132024-09-072023-03-162024-09-20CompliantTRUEFALSEFALSE
142024-09-302023-03-172024-09-21CompliantTRUEFALSEFALSE
152023-01-292023-03-182024-09-22Non-compliantFALSEFALSEFALSE
162023-03-162023-03-192024-09-23Non-compliantFALSEFALSEFALSE
172023-05-122023-07-122024-09-24CompliantFALSEFALSEFALSE
182023-11-102023-04-172024-09-25CompliantFALSEFALSEFALSE
192024-03-112024-10-192024-09-26CompliantTRUETRUEFALSE
202024-12-242024-04-032024-09-27Non-compliantTRUETRUEFALSE
212023-03-152023-11-212024-09-28CompliantFALSEFALSEFALSE
222024-11-162023-09-262024-09-29CompliantTRUEFALSEFALSE
232023-11-302024-10-252024-09-30Non-compliantFALSETRUEFALSE
242024-04-142024-02-122024-10-01CompliantTRUETRUEFALSE
Sheet3
Cell Formulas
RangeFormula
J5:K24J5=YEAR(F5:F24)>=YEAR($G$2)
L5:L24L5=H5:H24<=EOMONTH(G2,-(MONTH(G2)-1))
M5M5=SUMPRODUCT((YEAR(F5:F24)>=YEAR($G$2))*(YEAR(G5:G24)>=YEAR($G$2))*(H5:H24<=EOMONTH(G2,-(MONTH(G2)-1)))*(I5:I24="Compliant"))
Dynamic array formulas.
Click to expand...
 
Upvote 0
francoiscj1,

Please note: In the future, when marking a post as the solution, please mark the post containing the solution, not your own post acknowledging that some other post is the solution.
I have updated this thread for you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Training Completions by Month and Current Year
Replies
4
Views
140
awoohaw
awoohaw
F
  • Question
COUNTIF Formula
Replies
2
Views
122
francoiscj1
F
O
  • Solved
Year Month
Replies
4
Views
190
ozbeachbum
O
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
183
Trixterz
T
T
  • Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
182
Thlama
T

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

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