Countifs formula

1
votes

I am new to google sheets. I have a spreadsheet with two tabs that are involved with this question:

  • DB - Dental
  • RPT - Billing Dental

On the RPT tab, I am trying to create a countifs formula to count the number of patient records for which no billing activity has yet occurred.

My basic countifs formula looks like this:

=countifs('DB - Dental'!F3:F845,A3,'DB - Dental'!AC3:AC845,"")

Row 845 is the last row of data entered into the DB tab.

I now want to replace the 845 reference in this formula and make it instead automatically figure out what the last row number in my DB tab is so it will dynamically grow as more records are entered into the spreadsheet.

The formula I have for figuring out the last row of data in the DB tab is this:

=(max(arrayformula(if('DB - Dental'!A:A<>"",row('DB - Dental'!A:A),""))))

Both formulas work separately. The question is how can I merge them together so the countif function reads from row 3 to the last row of data in the DB tab?

Thank you very much for your help!

1
google-sheetscountifarray-formulas
Hey welcome to Stack! Review this on how to ask a question properly: stackoverflow.com/help/how-to-ask. Also if you post the sheet it will be easier to help you. Cheers – Chef1075

1 Answers

1
votes

Assumptions|

It would be much easier to have some sample data you are working on. Based on information you gave, the second formula you have written - 

=(max(arrayformula(if('DB - Dental'!A:A<>"",row('DB - Dental'!A:A),""))))

just counts rows that are not empty in A column.

If that is true,

  1. then you would archive the same just using

    =COUNTA('DB - Dental'!A:A)

  2. From this point you need to use this to generate final range in you basic countifs formula. You need INDIRECT function to be involved, like that:

    INDIRECT("'DB - Dental'!F3:F"&COUNTA("'DB - Dental'!A:A"))

Final formula

=countifs(
   INDIRECT("'DB - Dental'!F3:F" & COUNTA("'DB - Dental'!A:A")),A3,
   INDIRECT("'DB - Dental'!AC3:AC" & COUNTA("'DB - Dental'!A:A")),""
)

Should work - but if you gave your sample data, then I could verify it first... :)