Nel post
*Franz Verga* ha scritto:

Nel post
*Spreadsheet* ha scritto:

Ok, THanks Franz. Your solution does work. However, the reason why I
was getting a very large number is because I was using:
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

However, I only have data up until row 157. So I changed it to:
=SUMPRODUCT((ALL!AC2:AC157<(TODAY()-21))*(ALL!AG2:AG157=""))

How can the formula be modified so that it counts the number of blank
cells in AG that have the correct data in AC but doesn't add the
number of blank cells in AG that have no date in AC. In other words,
I don't want to have to manually change the 157 to 158 when I add a
new row.



I think this sould do the job:

=SUMPRODUCT((INDIRECT("ALL!AC2:AC"&COUNTA(AC2:AC)) <(TODAY()-21))*(INDIRECT("ALL!AG2:AG"&COUNTA(AG2:AG))=""))


The formula should be correct in this way:

=SUMPRODUCT((INDIRECT("ALL!AC2:AC"&COUNTA(AC:AC))< (TODAY()-21))*(INDIRECT("ALL!AG2:AG"&COUNTA(AG:AG))=""))

Errors due to copy & past... :-)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy