Frequency

[lambej12]

I have a multi column spread sheet with numbers, text and dates in
separate columns. Some of the fields under each column have a blank in
the column. I would like to find the frequency of the blank field in
the column based on the date year in another column. Something like a:
=CountIf(A2:A100,"=Blank") IF the corresponding(C2:C100,=2006)
In other words, I only want to count the occurances in A2 IF the
corresponding cell in C2 equals 2006.
I want to find the frequency of the blanks based on the year to
determine if users are entering data into a form field more often now
or in the past.
By the by, the tables that I am looking at, originate in Access. I am
pulling them into Excel for this function.
Thank you in advance for any help.

 

[Guest]

Assuming column C is formatted as DATE:

=SUMPRODUCT(--(A1:A20=" "),--(YEAR(C1:C20)=2006))

 

[Guest]

You can use a multi-step process to get the result.

Insert an extra column next to the one in which you want to count blank
entries. Adapt the following if statement to match the columns for date and
entry:

For this statement data begins on row 2; dates are in column A; entries are
in column B; the year

=IF(B2="",YEAR(A2),"")

Copy/fill the formula to the bottom of your data.

The result will be a year entry on every row that has a blank entry. Then
use the countif function to count the number of instences of each year in the
range.