SUMIFS or SUMPRODUCT or something else :(

[kapvg]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Sample dataset
3	Account	Weightage	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	TOTAL	Special condition
4	Acct 1	20.00%	$20,000		$20,000		$20,000		$20,000		$20,000		$20,000		$24,000	TRUE
5	Acct 2	50.00%		$10,000	$10,000	$10,000	$10,000	$10,000	$10,000						$30,000	TRUE
6	Acct 1	90.00%		$30,000		$30,000		$30,000		$30,000		$30,000		$30,000	$162,000	FALSE
7	Acct 2	30.00%										$30,000	$30,000		$18,000	FALSE
8	Acct 2	15.00%								$25,000	$25,000				$7,500	FALSE
9	Acct 3	10.00%	$15,000			$15,000			$15,000			$15,000			$6,000	FALSE
10	Acct 3	70.00%		$20,000			$20,000			$20,000			$20,000		$56,000	TRUE
11	Acct 3	80.00%			$30,000			$30,000			$30,000			$30,000	$96,000	FALSE
12	Acct 3	25.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,000	TRUE
13	Acct 2	50.00%			$50,000	$50,000	$50,000								$75,000	TRUE
14	Acct 1	75.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,000	TRUE
Sheet1

Cell Formulas
Range	Formula
P4:P14	P4	=SUM(Table1[@[Jan-21]:[Dec-21]])*[@Weightage]

Book1.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
17	Summary table - GOAL (currently prepared manually but need help to prepare this via formulae)
18	Account	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	TOTAL
19	Acct 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
20	Acct 2	$0	$5,000	$30,000	$30,000	$30,000	$5,000	$5,000	$0	$0	$0	$0	$0	$105,000
21	Acct 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
Range	Formula
D19:O19	D19	=IF($Q4=TRUE, D4*$C4, 0) + IF($Q6=TRUE, D6*$C6, 0) + IF($Q14=TRUE, D14*$C14, 0)
P19:P21	P19	=SUM(Table13[@[Jan-21]:[Dec-21]])
D20:O20	D20	=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:O21	D21	=IF($Q9=TRUE, D9*$C9, 0) + IF($Q10=TRUE, D10*$C10, 0) + IF($Q11=TRUE, D11*$C11, 0) + IF($Q12=TRUE, D12*$C12, 0)
D22	D22	=SUM([Jan-21])
E22	E22	=SUM([Feb-21])
F22	F22	=SUBTOTAL(109,[Mar-21])
G22	G22	=SUBTOTAL(109,[Apr-21])
H22	H22	=SUBTOTAL(109,[May-21])
I22	I22	=SUBTOTAL(109,[Jun-21])
J22	J22	=SUBTOTAL(109,[Jul-21])
K22	K22	=SUBTOTAL(109,[Aug-21])
L22	L22	=SUBTOTAL(109,[Sep-21])
M22	M22	=SUBTOTAL(109,[Oct-21])
N22	N22	=SUBTOTAL(109,[Nov-21])
O22	O22	=SUBTOTAL(109,[Dec-21])
P22	P22	=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

 

[kapvg]

Thanks for that.


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.

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

kapvg_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		Account	Weightage	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	TOTAL	Special condition
4		Acct 1	0.2	20000		20000		20000		20000		20000		20000		24000	TRUE
5		Acct 2	0.5		10000	10000	10000	10000	10000	10000						30000	TRUE
6		Acct 1	0.9		30000		30000		30000		30000		30000		30000	162000	FALSE
7		Acct 2	0.3										30000	30000		18000	FALSE
8		Acct 2	0.15								25000	25000				7500	FALSE
9		Acct 3	0.1	15000			15000			15000			15000			6000	FALSE
10		Acct 3	0.7		20000			20000			20000			20000		56000	TRUE
11		Acct 3	0.8			30000			30000			30000			30000	96000	FALSE
12		Acct 3	0.25	20000	20000	20000	20000	20000	20000	20000	20000	20000	20000	20000	20000	60000	TRUE
13		Acct 2	0.5			50000	50000	50000								75000	TRUE
14		Acct 1	0.75	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	90000	TRUE
15
16
17
18			Account	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	TOTAL
19			Acct 1	11500	7500	11500	7500	11500	7500	11500	7500	11500	7500	11500	7500	114000
20			Acct 2	0	5000	30000	30000	30000	5000	5000	0	0	0	0	0	105000
21			Acct 3	5000	19000	5000	5000	19000	5000	5000	19000	5000	5000	19000	5000	116000
Sheet1

Thanks for this.
I tried the solution in post #6 SUMIFS or SUMPRODUCT or something else :( and that one works as well.

Kudos to both experts ?

Can I mark 2 posts as solutions?

 

[Peter_SSs]

Can I mark 2 posts as solutions?

No, you just choose the one that suited you the best, or at the one you ended up using.

The main thing is that you got something that you are happy with.