Sumifs for multiple headers

[Pete3p0]

	A	B	C	D	E	F	G
1	Sales	Amount Sold			John	Paul	Adam
2	John			Apples
3	Apples	5		Oranges
4	Oranges	6		Pears
5	Pears	4
6
7	Paul
8	Apples	3
9	Oranges	8
10	Pears	5
11
12	Adam
13	Apples	5
14	Oranges	8
15	Pears	7

<tbody>
</tbody>

Hey guys,

I have a whole bunch of sales data. I'd like to consolidate it into the table on the right (D1:G4). In cell E2, I tried putting in this formula:

=SUMIFS($B:$B,$A:$A,$D2,$A:$A,E$1)

but it keeps returning a 0

Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

Hmm, I wonder how representative your data is?
If each person is only listed once in column A (like your sample) and each person has the same number of categories in the same order as each other (like your sample) then you could copy this formula across and down.

Excel Workbook
	A	B	C	D	E	F	G
1	Sales	Amount Sold			John	Paul	Adam
2	John			Apples	5	3	5
3	Apples	5		Oranges	6	8	8
4	Oranges	6		Pears	4	5	7
5	Pears	4
6
7	Paul
8	Apples	3
9	Oranges	8
10	Pears	5
11
12	Adam
13	Apples	5
14	Oranges	8
15	Pears	7
Table

 

[Pete3p0]

Hi Peter,

No the order can change and there might be different items under each name.

Table

	A	B	C	D	E	F	G
1	Sales	Amount Sold			John	Paul	Adam
2	John			Apples
3	Apples	5		Oranges
4	Oranges	6		Figs
5	Bananas	4		Mangoes
6	Watermelon	3		Pears
7				Watermelon
8	Paul
9	Apples	3
10	Oranges	8
11	Pears	5
12
13	Adam
14	Apples	5
15	Oranges	8
16	Mangoes	7
17	Figs	2
18

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:95px;"><col style="width:26px;"><col style="width:68px;"><col style="width:46px;"><col style="width:45px;"><col style="width:53px;"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

1. Does a name only ever appear once in column A or could John, for example, appear again for a second (or third, fourth etc) time further down the column?

2. Is there always a blank row before the next name like in both your sets of sample data?

3. If it turns out that a macro solution would be simplest, would that be acceptable to you?

 

[Pete3p0]

1. The names are all unique. IE John will only appear once
2. The blank row in this case will contain a total IE John's TOTAL --- 17
3. Perfectly acceptable.

 

[Peter_SSs]

2. The blank row in this case will contain a total IE John's TOTAL --- 17

If that means the layout is as shown below, then you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across & down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Tot(rData As Range, sName As String, sFruit As String) As Double
  Static d As Object
  Dim a As Variant
  Dim i As Long
  Dim s As String
  
  If d Is Nothing Then
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = 1
  End If
  a = rData.Value
  For i = 1 To UBound(a)
    If IsEmpty(a(i, 2)) Then
      s = a(i, 1)
    Else
      d(s & "|" & a(i, 1)) = a(i, 2)
    End If
  Next i
  Tot = d(sName & "|" & sFruit)
End Function

I presumed it was just an oversight that bananas was missing from your column D so I added it at the end.

Excel Workbook
	A	B	C	D	E	F	G
1	Sales	Amount Sold			John	Paul	Adam
2	John			Apples	5	3	5
3	Apples	5		Oranges	6	8	8
4	Oranges	6		Figs	0	0	2
5	Bananas	4		Mangoes	0	0	7
6	Watermelon	3		Pears	0	5	0
7		18		Watermelon	3	0	0
8	Paul			Bananas	4	0	0
9	Apples	3
10	Oranges	8
11	Pears	5
12		16
13	Adam
14	Apples	5
15	Oranges	8
16	Mangoes	7
17	Figs	2
18		22
Sheet1