Countifs for longer range

0
votes

I am currently using Countifs to add up an amount of registrants that have a gender criteria and birth year criteria.

Formula: =countifs(sheet1!N4:N90,”male”,sheet1!F4:F90,”1990”)

This works and shows me the amount of males born in 1990. I’m currently running this formula in sheet2. Every day or two I’ll import the registrations and the range will grow past row 90. Is there a way to write the formula that will adjust the result when I paste in new registrants?

Currently I can’t extend the formula past 90th row. And with all the birth years, it’s be great if I didn’t have to edit the formula manually every time I update the list.

1
excelexcel-formula
One method would be to have your data in a table (Insert-->Table) and then use structured references. The ranges would automatically extend as you add rows to the table. – Ron Rosenfeld

1 Answers

3
votes

There are at least 4 ways to do it:

  1. Use "N:N" and "F:F" instead of "N4:N90" and "F4:F90". It will refers to the whole column. 
    Formula: =countifs(sheet1!N:N,”male”,sheet1!F:F,”1990”)
  2. Use VBA (I'm not going to explain that now because I guess it's not what you want)

  3. Use Dynamic Named Range like explained here https://www.excel-easy.com/examples/dynamic-named-range.html.

    It applies to range without empty values (the height of the range is based on the number of values in the column and adding them to the row of the first row of your range (offset base).

    We don't know how your registrant records on sheet1 look. I guess that on column A you have a record ID or something else that it's not empty until the table ends. While on column N and F you can have empty values (e.g. registrant year or gender is not mandatory).

    So you have to create a named range 'Genders' that will refers to =OFFSET($N$1,0,0,COUNTA($A:$A),1) if you have not an header row. If you have one header row you have to start the offset from N2, etc. =OFFSET($N$2,0,0,COUNTA($A:$A),1).

    You have to do the same thing for the birth year and use these named ranges in your formula, like:

    Formula: =countifs(Genders,"male",BirthYears,"1990")
  4. Convert the range of records to a table, selecting it and going to Insert-->Table. You'll be able to use table field names in the formula (Solution based on Ron Rosenfeld' comment) 
    =countifs(Table1[Gender],"male",Table1[BirthYear],"1990")