Calculating Column Based Names

[Casp]

Hello,

I have a formula (below) that I put in a cell to test a sum calculation. I am using named ranges and have a dropdown list that has: All Departments, dept.1, dept2., dept3., etc etc etc. I plan to put this into a table.

This is what I have working so far in my test cells:
- In my dropdown, If I select all departments, it should calculate everything in the sold column for all departments
- To get total sold for users (will be put in table for comparison purposes), if I select a specific department, it calculates everything in the sold column for that specific department and the user listed for it

=SUM(SUMIFS(Sold,Users,{"Keith"},Department,DeptSelect),IF(DeptSelect="All Departments",SUM(Sold,Users,{"*Keith*"})))

Need help with:
- When I select all departments from my dropdown (named DeptSelect), then it should show the total of everything in the sold column for the specific user listed for it. The same user can be listed for different departments. I tried adding to the statement but the result was 0 and I believe I understand why but need to get the right syntax.

- I would like to calculate the sum of sold items by month or range of months (for example january and february) per user, per department. So for example, user Keith has 31 sold items for January but over time has 97 things sold.

I plan to have another table with the users in it and what theyve sold to compare monthly, quarterly etc.

Book1
	A	B	C	D
1	Date	Department	Sold
2	1/6/2021	Dept. 1	16	Keith
3	1/6/2021	Dept. 2	7	John
4	1/7/2021	Dept. 3	15	Keith
5	2/1/2021	Dept. 1	45	Tammy
6	2/2/2021	Dept. 2	66	John
7	2/3/2021	Dept. 3	32	Tammy
8	2/4/2021	Dept. 4	66	Keith
Sheet1

Thanks for any advice/help!

 

[Fluff]

How about

=SUMIFS(sold,Users,"Keith",Department,IF(DeptSelect="All departments","*",DeptSelect))

 

[Casp]

Thank you Fluff! That worked perfectly! Seems like SUMIFS was the only thing needed since it caters to multiple arguments/criteria. If I had another dropdown that listed every month, can I use EOMONTH in the formula above with the further condition to sum based on how many sold per month per user (hope that makes sense)?

 

[Fluff]

If I had another dropdown that listed every month, can I use EOMONTH in the formula

As long as the dropdown has actual dates, then yes.