Nested SUMIF or SUMPRODUCT help

[dancran]

Hey everyone,

I have this sample:

<title>Excel Jeanie HTML</title>

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Input:
2
3
4			Test	Selective Total		Mod 1		Mod 2		Mod 3		Total		Total
5	Labor	Location	Category	Hour	Value	Hour	Value	Hour	Value	Hour	Value	Hour	Value	Hour	Value
6	A	MD	Cool	0		0	0	1	10	0	0	1	10	0	0
7	B	MD	Cooler			0	0	2	20	0	0	2	20	0	0
8	C	CA	Uncool			0	0	3	30	5	50	8	80	0	0
9
10
Sheet1

What I am trying to do is create a formula for Column D which can be copied down for the rest of the rows.

I will focus on cell D6. I need this cell to show all of the hours worked based on the input of B1. I have a macro that hides columns based on the input of B1 (I.E.- will hide all columns not equal to the entry)

Code for macro (in case it is useful):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        FilterVal = Range("B1").Text
        'Turn off the flicker that's about to happen
        Application.ScreenUpdating = False
        
        'Loop Columns to End
        For ColIdx = 6 To UsedRange.Columns.Count
            If FilterVal = Empty Then
                Columns.Hidden = False
            'Look for the Column Label of row 6 to match filter
            ElseIf Cells(4, ColIdx) = FilterVal Then
                'If matched, then unhide it
                Columns(ColIdx).Hidden = False
                'increment the column index
                ColIdx = ColIdx + 1
                'Unhide that one too
                Columns(ColIdx).Hidden = False
            Else 'not the column label we're looking for
                'Hide 'em
                Columns(ColIdx).Hidden = True
                Columns(ColIdx + 1).Hidden = True
            End If
        Next
        'Turn the flicker back on
        Application.ScreenUpdating = True
    End If
End Sub

What I want is for cell D6 to count the number of hours worked based on the input of B1.

If nothing is entered into B1, it will be the same as the overall Total, but if, say Mod 2 is entered, I want cell D6 to add the hours worked for Labor Category A only for Mod 1 and Mod 2, not the rest of the sheet.

I tried using a nested SUMIF but quickly found out that is not possible. (First to limit what Mods to look at, then another to look at each Hour column). I saw SUMPRODUCT was a popular alternative but I am new to arrays and Excel syntax and have no idea how to write out a formula that will first limit which Mods to look at, then only add those Mods to find the Hours worked.

Could anyone help me out? If I need to reword what I am looking for just let me know.

Thanks,
Dan

 

[Aladin Akyurek]

Are you trying to sum the hour values per record?

 

[dancran]

I will give a few cases of what I am looking for overall.

Cell D6 and B1 = Mod 2:
0 (from Mod 1) + 1 (from Mod 2) = 1
Cell D7 and B1 = Mod 2:
0 (from Mod 1) + 2 (from Mod 2) = 2
Cell D8 and B1 = Mod 2:
0 (from Mod 1) + 3 (from Mod 2) = 3
Cell D8 and B1 = Mod 3:
0 (from Mod 1) + 3 (from Mod 2) + 5 (from Mod 3) = 8
Cell E6 and B1 = Mod 1:
0 (from Mod 1) = 0
Cell E6 and B1 = Mod 2:
0 (from Mod 1) + 10 (from Mod 2) = 10

Does that help at all? My real spreadsheet has many more columns and rows to select from so these examples are rather limited, I know.

 

[Aladin Akyurek]

Can we just get what the desired values in D6:D8 will be for the sample you posted?

 

[dancran]

Well the whole point is that they will change based on the entry in B1. There is no set value for D6:D8. Based on the sample table though the possible values are:
If B1 is blank:
D6 = 1
D7 = 2
D8 = 8
If B1 = "Mod 1":
D6 = 0
D7 = 0
D8 = 0
If B1 = "Mod 2":
D6 = 1
D7 = 2
D8 = 3
If B1 = "Mod 3":
D6 = 1
D7 = 2
D8 = 8

I did give you examples of D6:D8 when Mod 2 is entered in B1. I.E. - Only sum up to Mod 2 data. Does the help at all?

 

[dancran]

Bump. Can anyone help me try and create a formula that will accomplish these results?

 

[dancran]

Is there anything else I can do to help clarify what I am looking for/assist in solving this problem?

 

[Aladin Akyurek]

Is there anything else I can do to help clarify what I am looking for/assist in solving this problem?

You have 'Selective Total' in D4 and Hour in D5. These are difficult to concile with the outcome you specify:

If B1 = "Mod 2":
D6 = 1
D7 = 2
D8 = 3

Is 1 in D6 a sum, i.e., a selective total?