SUMPRODUCT - Summary Sheet Drop-Down Menus

[djmixer135]

Hi there,

I have two sheets on a workbook - a summary table and a master record table.

For each record on my master table, I want to add it to a count on my summary sheet (see below).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Summary Table[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]01/01/2018[/TD]
[TD]Cat A[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]01/02/2018[/TD]
[TD]Cat B[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]01/03/2018[/TD]
[TD]Cat A[/TD]
[/TR]
</tbody>[/TABLE]

Whilst I would normally perform this task using COUNTIFS(), this function does not support the functions MONTH() and YEAR(). Instead, I am using SUMPRODUCT.

So far, my count is working as expected for the 'Name' and 'Date' criteria. However, I would like to add a drop-down menu alongside my summary table allowing the user to select the summary of the different record types. 'Cat A', 'Cat B' or 'ALL CATEGORIES'.

Does anyone know how I would be able to do this?

Thanks in advanced!

 

[steve the fish]

With category in H1 and the other two tables copied and pasted into A1 place this into B2:

=SUMPRODUCT(--($A$9:$A$11=$A2),--(TEXT($B$9:$B$11,"mmm")=B$1),--($C$9:$C$11=$H$1))

Or just use a pivot table....

 

[djmixer135]

Hi Steve,

Thanks for this, this is very similar to what I have at the moment. However, in the event cell 'H1' = "EVERYTHING" or "ALL", how would I return a count for all of the categories in my master sheet?

 

[djmixer135]

I guess I need something along the lines of...

SUMPRODUCT(....................................................., IF($H$1<>"",--($C$9:$C$11=$H$1),--($C$9:$C$11<>"")))


Not sure how to write this though...

 

[steve the fish]

You could use an IF:

=IF($H$1="All",SUMPRODUCT(--($A$9:$A$11=$A2),--(TEXT($B$9:$B$11,"mmm")=B$1)),SUMPRODUCT(--($A$9:$A$11=$A2),--(TEXT($B$9:$B$11,"mmm")=B$1),--($C$9:$C$11=$H$1)))

 

[djmixer135]

Hi Steve,

Yes, I that would indeed work. Unfortunately, I am planning to add a few more dropdown lists for some additional columns I have. I do not want to have to rewrite the entire SUMPRODUCT formula for every combination of the drop-downs.

Is there anyway I can contain the IF statement within the individual arrays of the SUMPRODUCT?

Thanks for all your help so far.

 

[steve the fish]

It will use any criteria in H1 except for when H1 says 'All' when it will ignore the type and just use the date and name.

 

[djmixer135]

Hi Steve,

Thank you for all of your help. I think I have now found a solution (please see below)

=SUMPRODUCT(--($A$9:$A$11=$A2),--(TEXT($B$9:$B$11,"mmm")=B$1),--(($H$1="All")+($C$9:$C$11=$H$1)))