# Countif Dynamic Range

1. ## Countif Dynamic Range

Hello,

I have a large file with multiple embedded formulas that auto-update based on what Month / Year I input into A2 & B2. I then concatenate this Month and Year along with a static reference number to identify data on other sheets I need to reference - it ends up looking like 320161, 420161, etc.

In the example attached I need to write a formula in cell E4 that will count all of the values under the corresponding reference column on Sheet2. So for the given example I'm trying to get it to return 7.

I've tried using multiple different combinations of the Countif, Match, Address, Indirect, and offset functions but with no success. Can someone please help get me on the right track for how I would do this.

Please also note, this is just an example of what I'm trying to do on a much larger, confidential file.

Thank you!

2. ## Re: Countif Dynamic Range

Try

=SUMPRODUCT((Sheet2!\$B\$3:\$E\$10="X")*(Sheet2!\$B\$2:\$E\$2=Sheet1!\$E\$2))

3. ## Re: Countif Dynamic Range

=COUNTIF(INDEX(Sheet2!\$1:\$10,,MATCH(Sheet1!\$E\$2,Sheet2!2:2,FALSE)),"X")

4. ## Re: Countif Dynamic Range

Maybe this...

=COUNTA(INDEX(Sheet2!B3:E10,0,MATCH(E2,Sheet2!B2:E2,0)))

5. ## Re: Countif Dynamic Range

Thank you so much! Both the COUNTIF and COUNTA functions worked. I was, unfortunately, unable to get the SUMPRODUCT solution to work.

Many thanks!

6. ## Re: Countif Dynamic Range

You're welcome. We appreciate the feedback!

7. ## Re: Countif Dynamic Range

Great - glad to hear you were able to apply the formula.

For the SUMPRODUCT formula, you need to make sure that you have the same number of columns in each reference - for your example, that was B:E - and only one row in the second sheet2 reference

=SUMPRODUCT((Sheet2!\$B\$3:\$E\$10="X")*(Sheet2!\$B\$2:\$E\$2=Sheet1!\$E\$2))

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