# countif value in column based on date match in header

1. ## countif value in column based on date match in header

Hi,

Looking for a formula to count all occurrences in a column determined by match function on date in header.

I have names in column a, row 1 contains dates and row 2 sub headers booked and arrived. I would like to calculate how many y appear in a column based on the column determined by the match on the date in row 1 for booked and arrived and summarised i5 to k7 in a table.

Can someone please help?

Many thanks

2. ## Re: countif value in column based on date match in header

If you had the dates in both the Booked and Arrived columns (instead of just centered across both columns), you could use this formula in J5 and copy it into J6, J7 and K5:K7:

=SUMPRODUCT((\$B\$1:\$G\$1=\$I5)*(\$B\$2:\$G\$2=J\$4)*(\$B\$3:\$G\$17="y"))

- Moo

3. ## Re: countif value in column based on date match in header

Try this, copied down amd across...
=COUNTIF(OFFSET(\$B\$3,,MATCH(J\$4,\$B\$2:\$C\$2,0)-1+MATCH(\$I5,\$B\$1:\$G\$1,0)-1,COUNTA(\$A\$3:\$A\$17),1),"y")

4. ## Re: countif value in column based on date match in header

Using the suggestion in #2, you also can use a pivot table to get the result.

5. ## Re: countif value in column based on date match in header

j5=COUNTIF(INDEX(\$B\$3:\$G\$17,,MATCH(\$I5,\$B\$1:\$G\$1,0)+(J\$4<>"Booked")),"Y")
``Please Login or Register  to view this content.``
Trythis and copy across

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1