# COUNTIFS based on text and date

1. ## COUNTIFS based on text and date

I have a table of data with text strings. I need to count the number of instances of each text string, which I have done with a simple COUNTIF. But I also need to count them if the date in the column header is after today.

I have been trying COUNTIFS but can't get it right. Any help is appreciated.

Excel example.PNG

2. ## Re: COUNTIFS based on text and date

=SUMPRODUCT((\$A\$2:\$BB\$5=A9)*(\$A\$1:\$BB\$1>TODAY()))

but countif
=COUNTIF(\$A\$2:\$BB\$5,A9)

3. ## Re: COUNTIFS based on text and date

Change the formula in B9 to this:

=COUNTIF(\$2:\$5,A9)

(i.e. use \$ symbols in front of the row references so that they do not change). You can use this formula in cell C9:

=SUMPRODUCT((\$2:\$5=A9)*(\$1:\$1>=\$A\$7))

Copy both formulae down to the bottom of your key.

Hope this helps.

Pete

4. ## Re: COUNTIFS based on text and date

Thanks for the rep, given about an hour ago.

Pete

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