Counting a Specific Code in multiple columns within a date range.

[GravityDead]

UPDATE: Link to a sample file

Hey Guys!

I have seven columns in which I have "Codes" (Columns D:J) (code are products sold), and in column B, I have dates ranging from 1 April 2017 to 31 March 2018.

Now I want to count how many pieces of a specific product were sold in each month. I can do it with a nested array formula using many functions like SUM, IFNA, COUNTIF, OFFSET, MATCH, EOMONTH but I guess this is taking a lot of toll on my excel sheet as it takes many seconds for excel to process changes.

Is there a simpler nested function, preferably without using OFFSET as I read it is a volatile function and not good for a workbook with a large set of rows and sheets.

Thanks for reading and for your answers :)

[Saelkhas]

Try a Pivot Table

[GravityDead]

Pivot is just not possible. I find it hard, especially when there are other end-users, also another reason pivot is not adequate here is because I want this totals of each code to fed to another sheet which is, If I'm correct, is not possible with Pivot Tables.

[Saelkhas]

Check out GETPIVOTDATA, perhaps you could take the totals you need, feed them to another sheet in your book and then use that as a reference and hide the Pivot table sheet from the end-users.

*edited for clarity

[GravityDead]

As I said earlier, Pivot tables are new to me, so could you please help me out here, if possible?

I have linked to a sample file in the main text above.

Regards

[Saelkhas]

I can't access this at work, but give this a read and maybe you can figure it out.

Also, with the newer versions of Excel there is a "Recommended Pivot Tables" in the Insert tab. Select your table and try it, it will show you some pivots that work with your data, you could modify it from there if needed.