SUMPRODUCT - Summary Sheet Drop-Down Menus

djmixer135

New Member
Joined
Nov 19, 2018
Messages
13
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).

Summary TableJanFebMarAprMay
Name 100000
Name 200000
Name 311000
Name 400100

<tbody>
</tbody>


NameDateType
Name 301/01/2018Cat A
Name 301/02/2018Cat B
Name 401/03/2018Cat A

<tbody>
</tbody>

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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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....
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)))


 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top