COUNTIF / SUMPRODUCT with Multiple Criteria and/or Index Match

[bansaladman]

Hi all

I've been trying to come up with a solution for a little while but am just not seeing what I need to do to get there... I was hoping someone on these excellent forums may be able to assist?

Background:

I have 5 tables with 12 columns each (i.e. months) in my back pages. The 5 tables all contain the sales data for a particular branch (think 'Shop 1', 'Shop 2' etc). These data tables have the sales by product-code by month. Nothing too unique with this. Please see below (hope it's clear - i've only shown partial data from one table but the pattern is the same across all):

Sales Data:

A	B	C	D	E	F	G	H
1		Shop 1	Shop 1	Shop 1	Shop 1	Shop 1	Shop 1	etc..
2	Product Code	31-Jan	28-Feb	31-Mar	30-Apr	31-May	30-June	etc...
3	100	1	2	3	4	5	6
4	101	7	8	9	10	11	12
5	102	13	14	15	16	17	18

<tbody>
</tbody>

I have a summary sheet which gives a specified months' sales by Shop and by Product Code. There are multiple instances of each Product Code in the data - i.e. it is raw data. I have used a combination of SUMIF and INDEX/MATCH to achieve the summarised sales totals. I am happy with this output and that it is calculating correctly.

Summary Table:

A	B	C	D	E	F	G	H	I
1	Date:	[User Input]
2
3		Product Code	Count	Shop 1	Shop 2	Shop 3	Shop 4	Shop 5
4		100	???	[/TD] [TD][formula][/TD] [TD]etc[/TD] [TD]etc[/TD] [TD]etc[/TD] [/TR] [TR] [TD][COLOR=#0000FF][B]5[/B][/COLOR][/TD] [TD][/TD] [TD]101[/TD] [TD][B]???[/B][/TD] [TD][formula][/TD] [TD][formula][/TD] [TD]etc[/TD] [TD]etc[/TD] [TD]etc[/TD] [/TR] </tbody>[/TABLE] [U][B]The Problem[/B][/U] I quite simply want to be able to return a COUNT of each Product Code entry in the data, based on the same criteria I used in the SUMIF and INDEX MATCH used to return the total sales by month and shop. The formula in [B]E4[/B] is: {=SUMIFS(INDEX(Sales Data!$C$3:$Z$60,,MATCH(1,(Summary Table!E$3=Sales Data!$C$1:$Z$1)*(EOMONTH(Summary Table!$C$1,0)=Sales Data!$C$2:$Z$2),0)),Sales Data!$B$3:$B$60,Summary Table!$C4)} This is working as expected, however I cannot seem to replicate for the COUNTIF element... I have played around with COUNTIFS, SUMPRODUCT and a combination of SUMIF + SUMPRODUCT but I'm not getting what I need.. the other thing to note is that I only want to include non-zero or non-blank cells with this formula.. I hope above makes sense and that someone can assist! Thanks in advance.

<tbody>