Two Dimensional Conditional Counting

[VBAClueless]

In range B17:I20 in the attached I'm using a SUMPRODUCT formula to count the instances of Yes & No in the table above where certain criteria (Office, Date and Question Number) are met.
As you can see, the criteria are in a mix of rows and columns.
This works fine in small scale but when I'm dealing with 100 offices, many many questions and several years worth of data SUMPRODUCT becomes a little clunky and really slows things down.

I've done some digging around and found something that looks like an alternative using SUMIFS/INDEX/MATCH (it's on a different Excel forum so I won't post a link) but I cannot seem to translate that into my workbook as I'm not summing numeric values but counting text ones.

Does anyone know of an alternative to using SUMPRODUCT for this?

Many thanks.

[Phuocam]

Try this in cell B17:
=COUNTIFS($B$2:$B$9,$A$15,$A$2:$A$9,$A17,OFFSET($B$2:$B$9,,MATCH($B$15,$C$1:$E$1,0)),B$16)

[JohnTopley]

I moved your table to column J

In K2

=COUNTIFS($B:$B,$J$16,$A:$A,$J18,$C:$C,K$17)

Copy across and down BUT need to change $C:$C to $D:$D to each new question

[VBAClueless]

Thank you both. Both approaches work well.
Having to change the ranges for each question will prove to be a pain in the posterior as there are many questions, but it'll be a one time thing (hopefully) so once done it's done.

The first solution saves me that hassle but I'm a little worried about the volatile nature of OFFSET causing slowdown.

I shall give them both a try in the real workbook and go from there.
Many thanks again.

[JohnTopley]

I didn't use OFFSET because of its volatility (and the volatility of the your data): however, like many solutions, it is trade off between your time doing manual setup (changing ranges) versus the ability to copy formulae and copy across/down a range.

How often are you going to run the analysis? Once (or more) a day/week? If it is once a day, does it really matter if it takes Excel 5 or 15 minutes to calculate? Go get a cup of coffee while it's working!

[VBAClueless]

I couldn't agree with you more. I shall put the time in and set it up as per your solution and keep my fingers crossed that the powers that be do not want to change it in any way.

The analysis is run every morning. Sadly not a case of 5 or 15 minutes as the full workbook pulls data from one workbook per office and combines it into one master workbook.
When each office's workbook opens a calculation is forced (this is something necessary for those files to work properly but I'm not entirely sure why). This of course forces the master workbook to calculate too, meaning all the SUMPRODUCTS churning away over and over. Each import takes between 90 mins and 2 hrs!

[AliGW]

Can't you make the master workbook manual calculation only? That way you could delay any calculation until all of the data is entered.

[VBAClueless]

The master one is already set to manual calculation. Problem is each of the office workbooks forces a calculation automatically when they open. This in turn forces the master to calculate too.
If I could have ONLY the office workbooks calculate and not every other open workbook too then my issue would be solved.

I should really find out why they need to calculate when they open, but sadly the chap who put all this together has moved on.

[kvsrinivasamurthy]

Pl clarify
Does Months in B column will be in ascending order always.
Will be Office entry will be only once for each Month, like in your example.
In the second table count is for each quarter as shown.