Sumifs for multiple headers

P

Pete3p0

New Member
Joined
Jul 6, 2018
Messages
3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sales[/TD]
[TD]Amount Sold[/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Paul[/TD]
[TD]Adam[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Oranges[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pears[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Apples[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Oranges[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Pears[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Adam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Oranges[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Pears[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
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
ABCDEFG
1SalesAmount SoldJohnPaulAdam
2JohnApples535
3Apples5Oranges688
4Oranges6Pears457
5Pears4
6
7Paul
8Apples3
9Oranges8
10Pears5
11
12Adam
13Apples5
14Oranges8
15Pears7
Table
 
Last edited:
Upvote 0
Hi Peter,

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

Table


ABCDEFG
Sales
John

Apples
Apples
Oranges
Oranges
Figs
Bananas
Mangoes

Watermelon
 Pears


Watermelon
Paul





Apples




Oranges




Pears











Adam





Apples




Oranges




Mangoes




Figs

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1
[/TD]

[TD="align: right"] Amount Sold
 [/TD]

[TD="align: right"] John [/TD]
[TD="align: right"] Paul [/TD]
[TD="align: right"] Adam [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"] 2
 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]3
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9
[/TD]

[TD="align: right"]3
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10
[/TD]

[TD="align: right"]8
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11
[/TD]

[TD="align: right"]5
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14
[/TD]

[TD="align: right"]5
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15
[/TD]

[TD="align: right"]8
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16
[/TD]

[TD="align: right"]7
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17
[/TD]

[TD="align: right"]2
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18
[/TD]

[TD="align: right"][/TD]

</tbody>
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Pete3p0 said:
2. The blank row in this case will contain a total IE John's TOTAL --- 17
Click to expand...
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)

Code: 
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
ABCDEFG
1SalesAmount SoldJohnPaulAdam
2JohnApples535
3Apples5Oranges688
4Oranges6Figs002
5Bananas4Mangoes007
6Watermelon3Pears050
718Watermelon300
8PaulBananas400
9Apples3
10Oranges8
11Pears5
1216
13Adam
14Apples5
15Oranges8
16Mangoes7
17Figs2
1822
Sheet1
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
S
  • Solved
Getting excel to return multiple results from one cell
Replies
6
Views
710
Sparhan
S
M
  • Question Question
Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner
Replies
7
Views
399
maeyks
M
K
  • Question Question
Case sensitive distinct count based on multiple criteria
Replies
2
Views
293
shaowu459
shaowu459
P
  • Solved
SUMIFS function not filter values as i filter data from dropdown arrow
2 3
Replies
28
Views
685
pankajgrover
P

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

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