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:
[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Shop 1[/TD]
[TD]Shop 1[/TD]
[TD]Shop 1[/TD]
[TD]Shop 1[/TD]
[TD]Shop 1[/TD]
[TD]Shop 1[/TD]
[TD]etc..[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product Code[/TD]
[TD]31-Jan[/TD]
[TD]28-Feb[/TD]
[TD]31-Mar[/TD]
[TD]30-Apr[/TD]
[TD]31-May[/TD]
[TD]30-June[/TD]
[TD]etc...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]101[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]102[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G
[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date:[/TD]
[TD][User Input][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Product Code[/TD]
[TD]Count[/TD]
[TD]Shop 1[/TD]
[TD]Shop 2[/TD]
[TD]Shop 3[/TD]
[TD]Shop 4[/TD]
[TD]Shop 5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]???[/TD]
[TD][/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.