Sumif and Index

[Guest]

Hi,

I am new to this, and desperately need some help. I am trying to put together a workbook that compares multiple data sets. However, I have two variables that specify what information I need. I have two dropdown boxes. One is to specify the group of departments and the other is the month that I want to look at. The group of departments is a total of subdepartments. For example, there are 4 departments A,B,C,D that roll up to group A2 and three departments F,G,H that roll up to group A3. In my dropdown box, I specify that I want group A2 to be evaluated. When I choose A2, I sumif the 4 departments by account. But, I also want to choose what month's data I want to look at using another dropdown box. This is where I get stuck. How do I write a formula that sums by group by account for a specific month. I have tried separating the formula: in one cell, I have specified the group and account as related to the 1st dropdown box. In another cell, I am trying to sum all of the instances of that unique identifier in a given month. The given month, though, needs to be dynamic so that when I click on my month dropdown box, the information adjusts to that month.

I know it sounds convoluted, but is this even possible in Excel?

Thanks for your help!
C

 

[Frank Kabel]

Hi
you may use SUMPRODUCT for this. Not sure about your exact layout but
try something like
=SUMPRODUCT((A1:A100="group1")*(B1:B100="department")*(MONTH(C1:C100)=2
),D1100)

if you can procide some example rows of your data (plain text - no
attachment please) a more specific formula should easily be created

 

[Guest]

Hi Frank,

I will try that out.
The example

Datasheet 1 (actual spending):
Group, Department,Account, Jan, Feb, March
A2, 1, Salaries 1000,2000,3000
A2, 2, Salaries 200,300, 500
A2, 3, Salaries 400, 600, 700
A2, 4, Salaries 300,400, 550
A3, 5, Salaries 200,220,230
A3, 6, Salaries 220,280,290
A3, 5, Benefits 50,60,70

Datasheet 2 (forecast spending):
Group, Department,Account, Jan, Feb, March
A2, 1, Salaries 850,1500,2500
A2, 2, Salaries 150,290, 450
A2, 3, Salaries 450, 680, 710
A2, 4, Salaries 310,410, 550
A3, 5, Salaries 290,290,300
A3, 6, Salaries 210,290,310
A3, 5, Benefits 60,70,80

Calculation Sheet:
want to sum group A2's salaries line for the month of
Marchby using a dropdown box that has all of the months
already listed in it. When I pick March in the dropdown,
I want A2's salaries for March to sum. When I pick
January in the dropdown, I want A2's salaries to sum. All
three of these worksheets are in different books.

Thanks so much for your help!!
C