Formula to find % for times a date is under 6 months ago

[Ksilva]

Hi all,

I am trying to evaluate if a column on another sheet in the workbook’s date is less than 6 months ago and count them all up when the value (in this case it’s a person’s name) in one of the columns = the value I am looking for. I then what to split out the percentage of how often it was less than 6 months ago vs how many times the person’s name came up.

For example, whenever I see the name Roger, I want to count each time the column with the date is less than 6 months ago, and then calculate the percent it happened. So if Roger’s name comes up 6 times and 3 of those times the date is less than 6 months the value will yield as 50%.

Any ideas?

Thanks!

 

[joeu2004]

Something like this, if A2 is "Roger":

=IFERROR(COUNTIFS(Sheet2!$A$2:$A$1000, A2, Sheet2!$D$2:$D$1000, "<" & EDATE(TODAY(),-6)) / COUNTIF(Sheet2!$A$2:A$A1000, A2), "")

formatted as Percentage. IFERROR covers the case when (if) "Roger" does not appear at all. If that can never happen, you can omit the IFERROR(...,"") syntax.

 

[Ksilva]

Thank you, @Joeu! Unfortunately that didn't quite work but may have been user error on my end. I put in:
=IFERROR(COUNTIFS('Sheet2'!$Q$2:$Q$1000, F8, 'Sheet2'!$G$2:$G$1000, "<" & EDATE(TODAY(),-6)) / COUNTIF('Sheet2'!$Q$2:$Q$1000, F8), "") and the percentage that spit out was 60.87% whereas the manual calculation should have been 23%. I'm not sure what I'm doing wrong that made the percentage so off.

 

[joeu2004]

Off-hand, your formula looks okay. I would have to see an example Excel file (redacted) that demonstrates the difference: 60.87% v. 23%. If you're amenable, upload the file to a file-sharing website, and post the public/share URL in a response here. Test the download URL first, being careful to log out of the file-sharing website in order to ensure that anonymous access works. Alternatively, if the file constains sensitive data that you cannot redact easily, send it in email to me at joeu2004 "at" hotmail "dot" com.

As a wild-guess (and very unlikely, IMHO), I wonder if G2:G1000 is zero, not empty or text (typically the null string), in some rows where Q2:Q1000=F8 is true. Those would be counted as a match unintentionally, and you might have instintively excluded them in your manual count. Again, very unlikely, IMHO.

 

[Ksilva]

Hi Joe,

You nailed it! When I changed all of the blanks in the date column to a value greater than 6 months then the formula's value matched my manual count. How can I get the formula to read the blanks/zeroes as meaning it didn't meet the criteria of being within the last 6 months and thus should be counted against the %?

The dates are essentially whether or not someone has had a touchpoint in the last 6 months, so there are some instances where they may not have had a touchpoint at all, but I'd still want to calculate this. Thanks in advance!

 

[Ksilva]

@Joe - Tried again with a new data set and this works. Thanks so much for your help! Have a great weekend.

 

[joeu2004]

How can I get the formula to read the blanks/zeroes as meaning it didn't meet the criteria [...]?

=IFERROR(COUNTIFS(sheet2!$Q$2:$Q$1000, F8, sheet2!$G$2:$G$1000, ">0", sheet2!$G$2:$G$1000, "<" & EDATE(TODAY(),-6))
 / COUNTIFS(sheet2!$Q$2:$Q$1000, F8, sheet2!$G$2:$G$1000, ">0"), "")

or

=IFERROR(COUNTIFS(sheet2!$Q$2:$Q$1000, F8, sheet2!$G$2:$G$1000, ">0", sheet2!$G$2:$G$1000, "<" & EDATE(TODAY(),-6))
 / COUNTIF(sheet2!$Q$2:$Q$1000, F8), "")

Note quite sure what you mean by ``there are some instances where they may not have had a touchpoint at all, but I'd still want to calculate this``.

The first formula divides by the number of rows with the name in F8 and a number > 0 in column G (presumably a date).

The second formula divides by the number all rows with the name in F8, whether or not column G has a number.