Sum multiple criteria across rows and columns

G

Goddess

Board Regular
Joined
Dec 3, 2015
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have a table, from column A to E. The results I want to achieve is from column H to S. The only way I could think of is using sumifs+sumifs across the columns, but I have at least 30 columns in my actual database :(

sample3.xlsx
ABCDEFGHIJKLMNOPQRS
1FruitsAppleOrangeAppleGrapeTransportCarTruckTotal
2Unit of measurementkgtonnetonnekgkgtonnekgtonne
3TransportCarCarTruckTruckApple15101035
4Sent toSingaporeGermanySingaporeGermanyOrange9817
5Taken byJohnPeterLiamAmyGrape11617
6Jul 22 - Sep 227Cherry1919
7Oct 22 - Dec 2281088
8Jan 23 - Mar 23911
9Apr 23 - Jun 23Sent toSingaporeGermanyTotal
10Total1591011kgtonnekgtonne
11Apple152035
12FruitsOrangeCherryAppleOrange8917
13Unit of measurementkgkgtonneGrape11617
14TransportTruckTruckCarCherry1919
15Sent toGermanyGermanySingapore88
16Taken byJohnPeterLinda
17Jul 22 - Sep 225
18Oct 22 - Dec 229Taken byJohnPeterLiamAmyLindaTotal
19Jan 23 - Mar 23810kgtonnekgtonnekgtonnekgtonnekgtonne
20Apr 23 - Jun 232Apple15103735
21Total81970Orange8917
22Grape61117
23FruitsAppleGrapeCherry1919
24Unit of measurementtonnetonne88
25TransportCarTruck
26Sent toSingaporeGermany
27Taken byAmyPeter
28Jul 22 - Sep 2221
29Oct 22 - Dec 221
30Jan 23 - Mar 235
31Apr 23 - Jun 23
32Total0360
Sheet1
Cell Formulas
RangeFormula
I3I3=B10
L3,K14L3=D10
J3J3=D21+C32
J4J4=C10
L5L5=D32
K4K4=B21
K5K5=E10
K6K6=C21
M11:M14,M3:M6M3=SUM(I3:L3)
M7,B32:E32,S24,B21:E21,M15,B10:E10M7=SUM(M3:M6)
I11I11=B10
J11J11=C32+D10+D21
L12L12=C10
L13L13=D32
K12K12=B21
K13K13=E10
N20N20=D10
P20P20=C32
R20R20=D21
I20I20=B10
I21I21=B21
L21L21=C10
L22L22=D32
O22O22=E10
K23K23=C21
S20:S23S20=SUM(I20:R20)
Cells with Data Validation
CellAllowCriteria
A1:S32Any value


Thanks in advance!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
how about using index match like this?
Book1
ABCDEFGHIJKLMNOPQR
1FruitsAppleOrangeAppleGrapeTransportCarTruckTotal
2Unit of measurementkgtonnetonnekgkgtonnekgtonne
3TransportCarCarTruckTruckApple151001035
4Sent toSingaporeGermanySingaporeGermanyOrange098017
5Taken byJohnPeterLiamAmyGrape0011617
6Jul 22 - Sep 227Cherry0019019
7Oct 22 - Dec 2281088
8Jan 23 - Mar 23911
9Apr 23 - Jun 23Sent toSingaporeGermanyTotal
10Total1591011kgtonnekgtonne
11Apple15200035
12FruitsOrangeCherryAppleOrange008917
13Unit of measurementkgkgtonneGrape0011617
14TransportTruckTruckCarCherry0019019
15Sent toGermanyGermanySingapore88
16Taken byJohnPeterLinda
17Jul 22 - Sep 225
18Oct 22 - Dec 229Taken byJohnPeterLiamAmyLindaTotal
19Jan 23 - Mar 23810kgtonnekgtonnekgtonnekgtonnekgtonne
20Apr 23 - Jun 232Apple15000010030735
21Total81970Orange800900000017
22Grape0006001100017
23FruitsAppleGrapeCherry0019000000019
24Unit of measurementtonnetonne88
25TransportCarTruck
26Sent toSingaporeGermany
27Taken byAmyPeter
28Jul 22 - Sep 2221
29Oct 22 - Dec 221
30Jan 23 - Mar 235
31Apr 23 - Jun 23
32Total0360
Sheet1
Cell Formulas
RangeFormula
H3:I6H3=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G3=$B$1:$E$1)*($H$1=$B$3:$E$3)*(H$2=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G3=$B$12:$E$12)*($H$1=$B$14:$E$14)*(H$2=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G3=$B$23:$E$23)*($H$1=$B$25:$E$25)*(H$2=$B$24:$E$24),0)),0)
J3:K6J3=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G3=$B$1:$E$1)*($J$1=$B$3:$E$3)*(J$2=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G3=$B$12:$E$12)*($J$1=$B$14:$E$14)*(J$2=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G3=$B$23:$E$23)*($J$1=$B$25:$E$25)*(J$2=$B$24:$E$24),0)),0)
L3:L6,L11:L14L3=SUM(H3:K3)
L7,B32:E32,R24,B21:E21,L15,B10:E10L7=SUM(L3:L6)
H11:I14H11=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G11=$B$1:$E$1)*($H$9=$B$4:$E4)*(H$10=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G11=$B$12:$E$12)*($H$9=$B$15:$E$15)*(H$10=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G11=$B$23:$E$23)*($H$9=$B$26:$E$26)*(H$10=$B$24:$E$24),0)),0)
J11:K14J11=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G11=$B$1:$E$1)*($J$9=$B$4:$E$4)*(J$10=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G11=$B$12:$E$12)*($J$9=$B$15:$E$15)*(J$10=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G11=$B$23:$E$23)*($J$9=$B$26:$E$26)*(J$10=$B$24:$E$24),0)),0)
H20:I23H20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($H$18=$B$5:$E$5)*(H$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($H$18=$B$16:$E$16)*(H$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($H$18=$B$27:$E$27)*(H$19=$B$24:$E$24),0)),0)
J20:K23J20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($J$18=$B$5:$E$5)*(J$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($J$18=$B$16:$E$16)*(J$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($J$18=$B$27:$E$27)*(J$19=$B$24:$E$24),0)),0)
L20:M23L20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($L$18=$B$5:$E$5)*(L$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($L$18=$B$16:$E$16)*(L$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($L$18=$B$27:$E$27)*(L$19=$B$24:$E$24),0)),0)
N20:O23N20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($N$18=$B$5:$E$5)*(N$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($N$18=$B$16:$E$16)*(N$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($N$18=$B$27:$E$27)*(N$19=$B$24:$E$24),0)),0)
P20:Q23P20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($P$18=$B$5:$E$5)*(P$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($P$18=$B$16:$E$16)*(P$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($P$18=$B$27:$E$27)*(P$19=$B$24:$E$24),0)),0)
R20:R23R20=SUM(H20:Q20)
 
Upvote 0
ExceLoki said:
how about using index match like this?
Book1
ABCDEFGHIJKLMNOPQR
1FruitsAppleOrangeAppleGrapeTransportCarTruckTotal
2Unit of measurementkgtonnetonnekgkgtonnekgtonne
3TransportCarCarTruckTruckApple151001035
4Sent toSingaporeGermanySingaporeGermanyOrange098017
5Taken byJohnPeterLiamAmyGrape0011617
6Jul 22 - Sep 227Cherry0019019
7Oct 22 - Dec 2281088
8Jan 23 - Mar 23911
9Apr 23 - Jun 23Sent toSingaporeGermanyTotal
10Total1591011kgtonnekgtonne
11Apple15200035
12FruitsOrangeCherryAppleOrange008917
13Unit of measurementkgkgtonneGrape0011617
14TransportTruckTruckCarCherry0019019
15Sent toGermanyGermanySingapore88
16Taken byJohnPeterLinda
17Jul 22 - Sep 225
18Oct 22 - Dec 229Taken byJohnPeterLiamAmyLindaTotal
19Jan 23 - Mar 23810kgtonnekgtonnekgtonnekgtonnekgtonne
20Apr 23 - Jun 232Apple15000010030735
21Total81970Orange800900000017
22Grape0006001100017
23FruitsAppleGrapeCherry0019000000019
24Unit of measurementtonnetonne88
25TransportCarTruck
26Sent toSingaporeGermany
27Taken byAmyPeter
28Jul 22 - Sep 2221
29Oct 22 - Dec 221
30Jan 23 - Mar 235
31Apr 23 - Jun 23
32Total0360
Sheet1
Cell Formulas
RangeFormula
H3:I6H3=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G3=$B$1:$E$1)*($H$1=$B$3:$E$3)*(H$2=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G3=$B$12:$E$12)*($H$1=$B$14:$E$14)*(H$2=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G3=$B$23:$E$23)*($H$1=$B$25:$E$25)*(H$2=$B$24:$E$24),0)),0)
J3:K6J3=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G3=$B$1:$E$1)*($J$1=$B$3:$E$3)*(J$2=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G3=$B$12:$E$12)*($J$1=$B$14:$E$14)*(J$2=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G3=$B$23:$E$23)*($J$1=$B$25:$E$25)*(J$2=$B$24:$E$24),0)),0)
L3:L6,L11:L14L3=SUM(H3:K3)
L7,B32:E32,R24,B21:E21,L15,B10:E10L7=SUM(L3:L6)
H11:I14H11=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G11=$B$1:$E$1)*($H$9=$B$4:$E4)*(H$10=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G11=$B$12:$E$12)*($H$9=$B$15:$E$15)*(H$10=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G11=$B$23:$E$23)*($H$9=$B$26:$E$26)*(H$10=$B$24:$E$24),0)),0)
J11:K14J11=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G11=$B$1:$E$1)*($J$9=$B$4:$E$4)*(J$10=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G11=$B$12:$E$12)*($J$9=$B$15:$E$15)*(J$10=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G11=$B$23:$E$23)*($J$9=$B$26:$E$26)*(J$10=$B$24:$E$24),0)),0)
H20:I23H20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($H$18=$B$5:$E$5)*(H$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($H$18=$B$16:$E$16)*(H$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($H$18=$B$27:$E$27)*(H$19=$B$24:$E$24),0)),0)
J20:K23J20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($J$18=$B$5:$E$5)*(J$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($J$18=$B$16:$E$16)*(J$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($J$18=$B$27:$E$27)*(J$19=$B$24:$E$24),0)),0)
L20:M23L20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($L$18=$B$5:$E$5)*(L$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($L$18=$B$16:$E$16)*(L$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($L$18=$B$27:$E$27)*(L$19=$B$24:$E$24),0)),0)
N20:O23N20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($N$18=$B$5:$E$5)*(N$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($N$18=$B$16:$E$16)*(N$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($N$18=$B$27:$E$27)*(N$19=$B$24:$E$24),0)),0)
P20:Q23P20=IFERROR(INDEX($B$10:$E$10,MATCH(1,($G20=$B$1:$E$1)*($P$18=$B$5:$E$5)*(P$19=$B$2:$E$2),0)),0)+ IFERROR(INDEX($B$21:$E$21,MATCH(1,($G20=$B$12:$E$12)*($P$18=$B$16:$E$16)*(P$19=$B$13:$E$13),0)),0)+ IFERROR(INDEX($B$32:$E$32,MATCH(1,($G20=$B$23:$E$23)*($P$18=$B$27:$E$27)*(P$19=$B$24:$E$24),0)),0)
R20:R23R20=SUM(H20:Q20)
Click to expand...
Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
114
Cubist
Cubist
P
  • Solved
Finding Letter through Formula
Replies
2
Views
296
Pestomania
P
jski21
  • Solved
Find last entry in column and sum all prior rows
Replies
3
Views
147
Fluff
Fluff
V
  • Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
185
kvsrinivasamurthy
K
Craig Peter DG
  • Question
SUM OF DAILY TRANSACTION VALUES INTO MONTHLY SUMMARY - MULTIPLE CRITERIA
Replies
3
Views
175
Georgiboy
Georgiboy

Forum statistics

Threads
1,215,633
Messages
6,125,928
Members
449,274
Latest member
mrcsbenson

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