Count column w/blanks & values based on specific date

Hi.

I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access.

I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb).

I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.

Thank you in advance.

Nickelcell1

Rows() returns the number of rows in the specified range. Columns() returns the number of columns in the specified range. Both formulas do not have anything to contribute to your goal, i.e counting how many rows in a range of cells match a given date.

You probably need

=SUMPRODUCT(('TouchBack Detail'!B3:B12='Nov TouchBack Summary'!B1)*ISNUMBER('TouchBack Detail'!Q3:Q12))

Adjust ranges to suit.

The result is 2, because the third value in column Q is on a different date.


I'd advise against using whole column references in this kind of setup, since looking at the number of fields you plan to fill with similar formulas, you'll soon be looking at increasing calculation times. You may want to look into Pivot tables to report on the raw data instead.

Given use of XL2007 perhaps:

Please Login or Register  to view this content.

Even though COUNTIFS is quite efficient I would still concur that restricting ranges to some extent would be advisable.

If you don't need rows 26 & 27 you could just use AVERAGEIFS function for row 28, ie: =AVERAGEIFS('TouchBack Detail'!Q:Q,'TouchBack Detail'!$B:$B,B$1)

EDIT:
you state answer should be three rather than two - ie all rows should be included... in which case just use COUNTIF as opposed to COUNTIFS and check just the date column, =COUNTIF('TouchBack Detail'!$B:$B,B$1).