Calculating a percentage from Date of a cell range less than 15 days from today

[Brendan89]

Good morning,

I have searched through some threads and can't find an exact match for what I'm looking to do.

I have the incomplete formula below (which obviously doesn't work) however I can't seem to fit the date function into this formula.

=IF(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"},Risks!H3:H1000,-TODAY()<15))/SUM(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"}))

I need to generate a percentage where it checks the date range H3:H1000 and the date is 15 days previous to todays date AND G3:G1000 has any of the text information shown above. How do I fit this into a COUNTIFS formula, I can't figure it out?

Any help is appreciated!

It's worth noting there will be blanks in this range which aren't to be counted.

Thanks in Advance!

 

[Andrew_UK]

I don't like using Countifs when there are "Or" Conditions, in this instance you're trying to say countifs when column G is either "Identified","Assessed","Planned", or "In-Process" - right? I'd also really like to know what

I'd add a worker column at the end of your spreadsheet. If using Excel 2016 then do =Switch(G3,"Identified",1,"Assessed",1,"Planned",1,"In-Process",1,0) or if using Excel 2013 or earlier then do it using nested ifs.

Now your formula just becomes =Countifs([Worker Column],1,[Other Column],"Other Criteria")/Countif([Worker Column],1)

Your "Other Column/Criteria" clearly relate to some test of date - "Risks!H3:H1000,-TODAY()<15" - but this fomula as it stands will not be working for you. Could you talk us through what formula is in H and what it's supposed to be testing? The IF and the Sum in your original formula also seem superfluous and in the case of the IF it may well be causing some issues.

 

[Brendan89]

I don't like using Countifs when there are "Or" Conditions, in this instance you're trying to say countifs when column G is either "Identified","Assessed","Planned", or "In-Process" - right?

Correct.

I'd add a worker column at the end of your spreadsheet. If using Excel 2016 then do =Switch(G3,"Identified",1,"Assessed",1,"Planned",1,"In-Process",1,0) or if using Excel 2013 or earlier then do it using nested ifs.
That has just blown my mind and I never thought of doing it that way. Will it not work with a Countifs statement?

Now your formula just becomes =Countifs([Worker Column],1,[Other Column],"Other Criteria")/Countif([Worker Column],1)

Your "Other Column/Criteria" clearly relate to some test of date - "Risks!H3:H1000,-TODAY()<15" - but this fomula as it stands will not be working for you. Could you talk us through what formula is in H and what it's supposed to be testing? The IF and the Sum in your original formula also seem superfluous and in the case of the IF it may well be causing some issues.

So H3:H1000 is a date range, when an update is applied to the relevant row the user will update the date with todays date. What I need to measure is the % of these outside of 15 days from 'todays' date. Only measuring those where G3 range is "Identified","Assessed","Planned","In-Process".

I tried wrapping both in an IF and SUMIFS to test but the date capture is really catching me out...

 

[steve the fish]

See if this works. It only counts where it sees a value in column H3:H1000:

=SUM(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"},Risks!H3:H1000,">="&TODAY()-15)/SUM(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"},Risks!H3:H1000,"<>")))

 

[Brendan89]

See if this works. It only counts where it sees a value in column H3:H1000:

=SUM(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"},Risks!H3:H1000,">="&TODAY()-15)/SUM(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"},Risks!H3:H1000,"<>")))

That's worked an absolute charm, thanks! I'll save this for the future too!