SUMIFS or SUMPRODUCT or something else :(

K

kapvg

New Member
Joined
Jun 30, 2018
Messages
25
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I am stuck with trying to get the below dataset into a summary table using formulae (cannot use VBA as it is a marco-free file).

Book1.xlsx
BCDEFGHIJKLMNOPQ
2Sample dataset
3AccountWeightageJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTALSpecial condition
4Acct 120.00%$20,000$20,000$20,000$20,000$20,000$20,000$24,000TRUE
5Acct 250.00%$10,000$10,000$10,000$10,000$10,000$10,000$30,000TRUE
6Acct 190.00%$30,000$30,000$30,000$30,000$30,000$30,000$162,000FALSE
7Acct 230.00%$30,000$30,000$18,000FALSE
8Acct 215.00%$25,000$25,000$7,500FALSE
9Acct 310.00%$15,000$15,000$15,000$15,000$6,000FALSE
10Acct 370.00%$20,000$20,000$20,000$20,000$56,000TRUE
11Acct 380.00%$30,000$30,000$30,000$30,000$96,000FALSE
12Acct 325.00%$20,000$20,000$20,000$20,000$20,000$20,000$20,000$20,000$20,000$20,000$20,000$20,000$60,000TRUE
13Acct 250.00%$50,000$50,000$50,000$75,000TRUE
14Acct 175.00%$10,000$10,000$10,000$10,000$10,000$10,000$10,000$10,000$10,000$10,000$10,000$10,000$90,000TRUE
Sheet1
Cell Formulas
RangeFormula
P4:P14P4=SUM(Table1[@[Jan-21]:[Dec-21]])*[@Weightage]


Book1.xlsx
CDEFGHIJKLMNOP
17Summary table - GOAL (currently prepared manually but need help to prepare this via formulae)
18AccountJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTAL
19Acct 1$11,500$7,500$11,500$7,500$11,500$7,500$11,500$7,500$11,500$7,500$11,500$7,500$114,000
20Acct 2$0$5,000$30,000$30,000$30,000$5,000$5,000$0$0$0$0$0$105,000
21Acct 3$5,000$19,000$5,000$5,000$19,000$5,000$5,000$19,000$5,000$5,000$19,000$5,000$116,000
22$16,500$31,500$46,500$42,500$60,500$17,500$21,500$26,500$16,500$12,500$30,500$12,500$335,000
Sheet1
Cell Formulas
RangeFormula
D19:O19D19=IF($Q4=TRUE, D4*$C4, 0) + IF($Q6=TRUE, D6*$C6, 0) + IF($Q14=TRUE, D14*$C14, 0)
P19:P21P19=SUM(Table13[@[Jan-21]:[Dec-21]])
D20:O20D20=IF($Q5=TRUE, D5*$C5, 0) + IF($Q7=TRUE, D7*$C7, 0) + IF($Q8=TRUE, D8*$C8, 0) + IF($Q13=TRUE, D13*$C13, 0)
D21:O21D21=IF($Q9=TRUE, D9*$C9, 0) + IF($Q10=TRUE, D10*$C10, 0) + IF($Q11=TRUE, D11*$C11, 0) + IF($Q12=TRUE, D12*$C12, 0)
D22D22=SUM([Jan-21])
E22E22=SUM([Feb-21])
F22F22=SUBTOTAL(109,[Mar-21])
G22G22=SUBTOTAL(109,[Apr-21])
H22H22=SUBTOTAL(109,[May-21])
I22I22=SUBTOTAL(109,[Jun-21])
J22J22=SUBTOTAL(109,[Jul-21])
K22K22=SUBTOTAL(109,[Aug-21])
L22L22=SUBTOTAL(109,[Sep-21])
M22M22=SUBTOTAL(109,[Oct-21])
N22N22=SUBTOTAL(109,[Nov-21])
O22O22=SUBTOTAL(109,[Dec-21])
P22P22=SUBTOTAL(109,[TOTAL])


Essentially, I want to get the weighted total per month for each account only when the 'Special Condition' = TRUE. I have been scouring the web, forums for a couple of hours and tried various SUMIFS and SUMPRODUCT combinations before giving up :(

Please advise if there is a formula based approach that can be used to prepare the summary table as shown above.

thanks!!
~kg
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
How about:

Dante Amor
ABCDEFGHIJKLMNOP
1Sample dataset
2AccountWeightageJan-21feb-21mar-21Apr-21may-21jun-21jul-21Aug-21sep-21oct-21nov-21Dec-21TOTALSpecial condition
3Acct 120%20000200002000020000200002000024000VERDADERO
4Acct 250%10000100001000010000100001000030000VERDADERO
5Acct 190%300003000030000300003000030000162000FALSO
6Acct 230%300003000018000FALSO
7Acct 215%25000250007500FALSO
8Acct 310%150001500015000150006000FALSO
9Acct 370%2000020000200002000056000VERDADERO
10Acct 380%3000030000300003000096000FALSO
11Acct 325%20000200002000020000200002000020000200002000020000200002000060000VERDADERO
12Acct 250%50000500005000075000VERDADERO
13Acct 175%10000100001000010000100001000010000100001000010000100001000090000VERDADERO
14
15
16
17Summary table - GOAL (currently prepared manually but need help to prepare this via formulae)
18AccountJan-21feb-21mar-21Apr-21may-21jun-21jul-21Aug-21sep-21oct-21nov-21Dec-21TOTAL
19Acct 1300001000030000100003000010000300001000030000100003000010000
20Acct 2010000600006000060000100001000000000
21Acct 3200004000020000200004000020000200004000020000200004000020000
Sheet1
Cell Formulas
RangeFormula
B19:B21B19=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[[#This Row],[Account]:[Account]])* (Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Jan-21]])* (Table1[[Special condition]:[Special condition]]=TRUE)* (Table1[[Jan-21]:[Dec-21]]))
 
Upvote 0
DanteAmor said:
How about:

Dante Amor
ABCDEFGHIJKLMNOP
1Sample dataset
2AccountWeightageJan-21feb-21mar-21Apr-21may-21jun-21jul-21Aug-21sep-21oct-21nov-21Dec-21TOTALSpecial condition
3Acct 120%20000200002000020000200002000024000VERDADERO
4Acct 250%10000100001000010000100001000030000VERDADERO
5Acct 190%300003000030000300003000030000162000FALSO
6Acct 230%300003000018000FALSO
7Acct 215%25000250007500FALSO
8Acct 310%150001500015000150006000FALSO
9Acct 370%2000020000200002000056000VERDADERO
10Acct 380%3000030000300003000096000FALSO
11Acct 325%20000200002000020000200002000020000200002000020000200002000060000VERDADERO
12Acct 250%50000500005000075000VERDADERO
13Acct 175%10000100001000010000100001000010000100001000010000100001000090000VERDADERO
14
15
16
17Summary table - GOAL (currently prepared manually but need help to prepare this via formulae)
18AccountJan-21feb-21mar-21Apr-21may-21jun-21jul-21Aug-21sep-21oct-21nov-21Dec-21TOTAL
19Acct 1300001000030000100003000010000300001000030000100003000010000
20Acct 2010000600006000060000100001000000000
21Acct 3200004000020000200004000020000200004000020000200004000020000
Sheet1
Cell Formulas
RangeFormula
B19:B21B19=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[[#This Row],[Account]:[Account]])* (Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Jan-21]])* (Table1[[Special condition]:[Special condition]]=TRUE)* (Table1[[Jan-21]:[Dec-21]]))
Click to expand...
Thanks Dante, your solution takes into account the TRUE or FALSE in the 'Special Condition' column but I am still at a loss on how to include the % weights from the 'Weightage' column in the calculation in order to multiply with the currency amounts for each month. Any suggestions ?
 
Upvote 0
I was able to get to my required solution with a small addition to your formula :)

Final solution below:

Book1.xlsx
CDEFGHIJKLMNOP
26Summary table - GOAL (currently prepared manually but need help to prepare this via formulae)
27AccountJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTAL
28Acct 1$11,500$7,500$11,500$7,500$11,500$7,500$11,500$7,500$11,500$7,500$11,500$7,500$114,000
29Acct 2$0$5,000$30,000$30,000$30,000$5,000$5,000$0$0$0$0$0$105,000
30Acct 3$5,000$19,000$5,000$5,000$19,000$5,000$5,000$19,000$5,000$5,000$19,000$5,000$116,000
31$16,500$31,500$46,500$42,500$60,500$17,500$21,500$26,500$16,500$12,500$30,500$12,500$335,000
Sheet1
Cell Formulas
RangeFormula
D28:D30D28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Jan-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
E28:E30E28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Feb-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
F28:F30F28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Mar-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
G28:G30G28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Apr-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
H28:H30H28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[May-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
I28:I30I28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Jun-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
J28:J30J28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Jul-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
K28:K30K28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Aug-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
L28:L30L28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Sep-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
M28:M30M28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Oct-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
N28:N30N28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Nov-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
O28:O30O28=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Dec-21]])*(Table1[[Special condition]:[Special condition]]=TRUE)*(Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
P28:P30P28=SUM(Table13[@[Jan-21]:[Dec-21]])
D31D31=SUM([Jan-21])
E31E31=SUM([Feb-21])
F31F31=SUBTOTAL(109,[Mar-21])
G31G31=SUBTOTAL(109,[Apr-21])
H31H31=SUBTOTAL(109,[May-21])
I31I31=SUBTOTAL(109,[Jun-21])
J31J31=SUBTOTAL(109,[Jul-21])
K31K31=SUBTOTAL(109,[Aug-21])
L31L31=SUBTOTAL(109,[Sep-21])
M31M31=SUBTOTAL(109,[Oct-21])
N31N31=SUBTOTAL(109,[Nov-21])
O31O31=SUBTOTAL(109,[Dec-21])
P31P31=SUBTOTAL(109,[TOTAL])
 
Upvote 0
Or possibly this?

Edit: Mini-sheet removed as I had not copied my formulas across correctly. I will propose another option shortly
 
Last edited:
Upvote 0
kapvg said:
how to include the % weights from the 'Weightage' column in the calculation in order to multiply with the currency amounts for each month.
Click to expand...

Try:
varios 14ago2021.xlsm
AB
18AccountJan-21
19Acct 111500
20Acct 20
21Acct 35000
Sheet1
Cell Formulas
RangeFormula
B19:B21B19=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[[#This Row],[Account]:[Account]])* (Table1[[#Headers],[Jan-21]:[Dec-21]]=Table13[[#Headers],[Jan-21]])* (Table1[[Special condition]:[Special condition]]=TRUE)* (Table1[[Jan-21]:[Dec-21]])*(Table1[[Weightage]:[Weightage]]))
 
Upvote 0
Solution
DanteAmor said:
Something is wrong with your formula.
For March, Acct 2, the sum is 60,000. 50% is 30,000. Your result is 5,000.
Click to expand...
Yes, Dante, I had just realised this and removed the mini-sheet.

@kapvg
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Microsoft 365 you could try this version (in D19 for me), copied across and down.
Excel Formula: 
=SUM(FILTER(Table1[Jan-21]*Table1[[Weightage]:[Weightage]]*Table1[[Special condition]:[Special condition]],Table1[[Account]:[Account]]=Table13[@[Account]:[Account]]))


kapvg.xlsm
ABCDEFGHIJKLMNOPQ
1
2
3AccountWeightageJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTALSpecial condition
4Acct 10.220000200002000020000200002000024000TRUE
5Acct 20.510000100001000010000100001000030000TRUE
6Acct 10.9300003000030000300003000030000162000FALSE
7Acct 20.3300003000018000FALSE
8Acct 20.1525000250007500FALSE
9Acct 30.1150001500015000150006000FALSE
10Acct 30.72000020000200002000056000TRUE
11Acct 30.83000030000300003000096000FALSE
12Acct 30.2520000200002000020000200002000020000200002000020000200002000060000TRUE
13Acct 20.550000500005000075000TRUE
14Acct 10.7510000100001000010000100001000010000100001000010000100001000090000TRUE
15
16
17
18AccountJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTAL
19Acct 1115007500115007500115007500115007500115007500115007500114000
20Acct 2050003000030000300005000500000000105000
21Acct 35000190005000500019000500050001900050005000190005000116000
Sheet1
 
Upvote 0
Peter_SSs said:
@kapvg
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Microsoft 365 you could try this version (in D19 for me), copied across and down.
Excel Formula: 
=SUM(FILTER(Table1[Jan-21]*Table1[[Weightage]:[Weightage]]*Table1[[Special condition]:[Special condition]],Table1[[Account]:[Account]]=Table13[@[Account]:[Account]]))


kapvg.xlsm
ABCDEFGHIJKLMNOPQ
1
2
3AccountWeightageJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTALSpecial condition
4Acct 10.220000200002000020000200002000024000TRUE
5Acct 20.510000100001000010000100001000030000TRUE
6Acct 10.9300003000030000300003000030000162000FALSE
7Acct 20.3300003000018000FALSE
8Acct 20.1525000250007500FALSE
9Acct 30.1150001500015000150006000FALSE
10Acct 30.72000020000200002000056000TRUE
11Acct 30.83000030000300003000096000FALSE
12Acct 30.2520000200002000020000200002000020000200002000020000200002000060000TRUE
13Acct 20.550000500005000075000TRUE
14Acct 10.7510000100001000010000100001000010000100001000010000100001000090000TRUE
15
16
17
18AccountJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTAL
19Acct 1115007500115007500115007500115007500115007500115007500114000
20Acct 2050003000030000300005000500000000105000
21Acct 35000190005000500019000500050001900050005000190005000116000
Sheet1
Click to expand...
@Peter_SSs - updated the Office versions in my profile. I primarily work with 2016 and 2019 but come across Office 365 files when working on files directly on a SharePoint or on MS Teams.

I've recently started using 'Tables' in MS Excel and find them fascinating and confusing at the same time. :)
 
Upvote 0
kapvg said:
updated the Office versions in my profile.
Click to expand...
Thanks for that. (y)

kapvg said:
I primarily work with 2016 and 2019
Click to expand...
In that case you would not be able to use the formula I suggested in post #8 but you could try this one, again copied across and down.

Excel Formula: 
=SUMPRODUCT((Table1[[Account]:[Account]]=Table13[@[Account]:[Account]])*(Table1[[Special condition]:[Special condition]])*Table1[[Weightage]:[Weightage]]*Table1[Jan-21])

kapvg_1.xlsm
ABCDEFGHIJKLMNOPQ
1
2
3AccountWeightageJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTALSpecial condition
4Acct 10.220000200002000020000200002000024000TRUE
5Acct 20.510000100001000010000100001000030000TRUE
6Acct 10.9300003000030000300003000030000162000FALSE
7Acct 20.3300003000018000FALSE
8Acct 20.1525000250007500FALSE
9Acct 30.1150001500015000150006000FALSE
10Acct 30.72000020000200002000056000TRUE
11Acct 30.83000030000300003000096000FALSE
12Acct 30.2520000200002000020000200002000020000200002000020000200002000060000TRUE
13Acct 20.550000500005000075000TRUE
14Acct 10.7510000100001000010000100001000010000100001000010000100001000090000TRUE
15
16
17
18AccountJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTAL
19Acct 1115007500115007500115007500115007500115007500115007500114000
20Acct 2050003000030000300005000500000000105000
21Acct 35000190005000500019000500050001900050005000190005000116000
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
How to compare similar ratings?
Replies
0
Views
471
JenniferMurphy
JenniferMurphy
R
  • Question Question
LOOKUP IN VBA ARRAY AND DICTIONARY
Replies
7
Views
751
GlennUK
G
K
  • Solved
Extract values from different columns
Replies
4
Views
630
Peter_SSs
Peter_SSs
Ramballah
  • Question Question
Small formula for calculation
2
Replies
10
Views
1K
Ramballah
Ramballah
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,823
Messages
6,181,171
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