Advanced Sumproduct / Match

R

ryansm05

Board Regular
Joined
Sep 14, 2016
Messages
148
Office Version
  1. 365
Platform
  1. Windows
Hi,

I need a formula that will sum a range based on three criteria:

1) Project Code (D7:D17)
2) Region (C7:C17)
3) Period (E5:P5)

I tried a Sumproduct but it freaked out with three criteria :/ - I need this to obviously look at all three corresponding columns (dependent on whether it's Q1 etc)

https://www.dropbox.com/s/v5r0tsdksghli5w/SumHelp.xlsx?dl=0
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Maybe...

E21 copied across and down
=SUMPRODUCT(($C$7:$C$17=$C21)*($D$7:$D$17=$D21)*($E$5:$P$5=E$20),$E$7:$P$17)

M.
 
Upvote 0
Clickbait!


Book1
CDEFGHIJKLMNOP
5Q1Q1Q1Q2Q2Q2Q3Q3Q3Q4Q4Q4
6AreaCode
7P&P51574574
8R&R51
9P&P5277545454777454457457457
10P&P536547474574557
11P&P54
12R&R54
13C&C5454754745774754457
14P&P5557775447457547447
15C&C56574457477547457457
16R&R57475475475
17
18
19
20Criteria 1Criteria 2Q1Q2
21P&P52815628
22R&R5400
23R&R5705508
Sheet2
Cell Formulas
RangeFormula
E21{=SUM(IF($C$7:$C$16=$C21,IF($D$7:$D$16=$D21,IF($E$5:$P$5=E$20,$E$7:$P$16))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
s=#REF!#REF!


WBD
 
Upvote 0
wideboydixon said:
Clickbait!

CDEFGHIJKLMNOP
Q1Q1Q1Q2Q2Q2Q3Q3Q3Q4Q4Q4
AreaCode
P&P51
R&R51
P&P52
P&P53
P&P54
R&R54
C&C54
P&P55
C&C56
R&R57
Criteria 1Criteria 2Q1Q2
P&P52
R&R54
R&R57

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]

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

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

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]9[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]754[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]547[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]547[/TD]
[TD="align: right"][/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"]745[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

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

[TD="align: center"]12[/TD]

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

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]754[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]754[/TD]
[TD="align: right"]457[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]7754[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7457[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5474[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]47[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]574[/TD]
[TD="align: right"]4574[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4754[/TD]
[TD="align: right"]754[/TD]
[TD="align: right"][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

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

[TD="align: center"]18[/TD]

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

[TD="align: center"]19[/TD]

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

[TD="align: center"]20[/TD]

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

[TD="align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]815[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]628[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

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

[TD="align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]5508[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E21[/TH]
[TD="align: left"]{=SUM(IF($C$7:$C$16=$C21,IF($D$7:$D$16=$D21,IF($E$5:$P$5=E$20,$E$7:$P$16))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]s[/TH]
[TD="align: left"]=#REF!#REF![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD
Click to expand...

@wideboydixon

What a fantastic formula - easy to follow/understand too.

Thanks so much!
Ryan
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Extract Comma-Separated Matches Based on Multiple Criteria and Order Preservation
2
Replies
10
Views
740
sobuj53
S
L
  • Solved
Cell reference
Replies
3
Views
465
Peter_SSs
Peter_SSs
A
  • Question Question
Populate data based on matching last part with sheet name across sheets
Replies
2
Views
111
Ali M
A
A
  • Solved
Help with macros to open many files, and extract data. Need help with match / find.
Replies
9
Views
1K
johnnyL
J
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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