CODEWITHSUNDEEP
arraysgoogle-sheetsfiltergoogle-sheets-formuladatediff
Harry Norman
Harry Norman

Reputation: 195

Use CountUnique when age is 18yr-30yr and is male/female

I need to calculate the number of unique phone numbers when the age is between X-X and is Male/Female, output into a table of results.

Here's the spreadsheet of test data to enter your formula - https://docs.google.com/spreadsheets/d/1H8LG1p14DHgYWUdykMGatVHzWZPmju-j2EcO0_u9PIA/edit?usp=sharing

from:

0

create:

0

Upvotes: 0

Views: 96

Answers (2)

Kate
Kate

Reputation: 1533

To get age, use DATEDIF(Data!$B$2:$B, TODAY(), "Y"). Then use COUNTUNIQUE with FILTER to get your result, wrapping with IFERROR to default to 0 in the case where there are no matches.

You can parameterize the min/max age if you want, but here's the hardcoded formulas you can paste into A2, B2, and C2 on your Result sheet and then copy down to row 3 to fill out your table:

Result!A2 (18-25yr): =COUNTUNIQUE(IFERROR(FILTER(Data!$A$2:$A, DATEDIF(Data!$B$2:$B, TODAY(), "Y")>=18, DATEDIF(Data!$B$2:$B, TODAY(), "Y")<26, Data!$C$2:$C=$A2)))

Result!B2 (26-45yr): =COUNTUNIQUE(IFERROR(FILTER(Data!$A$2:$A,DATEDIF(Data!$B$2:$B, TODAY(), "Y")>=26, DATEDIF(Data!$B$2:$B, TODAY(), "Y")<46, Data!$C$2:$C=$A2)))

Result!C2 (46yr and over): =COUNTUNIQUE(IFERROR(FILTER(Data!$A$2:$A, DATEDIF(Data!$B$2:$B, TODAY(), "Y")>=46, Data!$C$2:$C=$A2)))

View workbook copy with working solution.

Update: Alternative Solution with Dynamic Age Ranges

If you insert an additional row above your result table to specify the minimum age you want included in that column, you can simplify the formula used throughout your result table to be the same for each column.

Alternative Solution with Dynamic Age Ranges

The following solution assumes the same table structure as the original result table, with the exception that B1, C1, and D1 contains minimum age shown in column (i.e., 18, 26, and 46 respectively), and that the table header starts in row 2.

To construct the new column headers, enter the following into B2, then copy across to C2 and D2:

  • =IF(B$1="",, B$1&IF(C$1="", "yr and over", "-"&(C$1-1)&"yr"))

To calculate your result values, enter the following into B3, then copy across to columns C3 and D3, then down to row 4:

  • =COUNTUNIQUE(IFERROR(FILTER(Data!$A$2:$A, DATEDIF(Data!$B$2:$B, TODAY(), "Y")>=B$1, DATEDIF(Data!$B$2:$B, TODAY(), "Y")<IF(C$1="", 9999, C$1), Data!$C$2:$C=$A3)))

View alternative solution using dynamic age ranges ("Result (Parameterized)").

Upvotes: 2

player0
player0

Reputation: 1

while the previous answer is promising it holds a major flaw because FILTER is not wrapped in IFERROR the COUNTUNIQUE will count #N/A error (eg. no results from filter) as 1 instead of 0, therefore:

use this formula in B2 cell:

={{COUNTUNIQUE(IFNA(FILTER(Data!A2:A, Data!C2:C=A2,
   DATEDIF(Data!B2:B, TODAY(), "Y")>=B1, DATEDIF(Data!B2:B, TODAY(), "Y")<C1)));
   COUNTUNIQUE(IFNA(FILTER(Data!A2:A, Data!C2:C=A3,
   DATEDIF(Data!B2:B, TODAY(), "Y")>=B1, DATEDIF(Data!B2:B, TODAY(), "Y")<C1)))},
  {COUNTUNIQUE(IFNA(FILTER(Data!A2:A, Data!C2:C=A2,
   DATEDIF(Data!B2:B, TODAY(), "Y")>=C1, DATEDIF(Data!B2:B, TODAY(), "Y")<D1)));
   COUNTUNIQUE(IFNA(FILTER(Data!A2:A, Data!C2:C=A3,
   DATEDIF(Data!B2:B, TODAY(), "Y")>=C1, DATEDIF(Data!B2:B, TODAY(), "Y")<D1)))},
  {COUNTUNIQUE(IFNA(FILTER(Data!A2:A, Data!C2:C=A2,
   DATEDIF(Data!B2:B, TODAY(), "Y")>=D1)));
   COUNTUNIQUE(IFNA(FILTER(Data!A2:A, Data!C2:C=A3,
   DATEDIF(Data!B2:B, TODAY(), "Y")>=D1)))}}

0

spreadsheet demo

Upvotes: 0

Related Questions