SUMIFS formula

[Sean15]

Hello:

Excel 2010
	C	D	E	F
2	Amount	Posting layer	Status
3	1,250.00	BF from 9/30/17
4	2,900.00	Oct-17 c-card
5	294.49	Oct-17 c-card	cleared
6	111.87	Oct-17 c-card	cleared
7	394.96	Nov-17 c-card	cleared
8	141.97	Nov-17 c-card
9	406.62	Nov-17 c-card	cleared
10	1,445.00	Nov-17 c-card	cleared
11	1,897.27	Nov-17 c-card	cleared
12	1,976.00	Dec-17 c-card
13	451.88	Dec-17 c-card
14	510.32	Dec-17 c-card
15	255.16	Dec-17 c-card
16	399.00	Jan-18 c-card	cleared
17	399.00	Jan-18 c-card	cleared
18	399.00	Jan-18 c-card
19	399.00	Jan-18 c-card
20	13,631.54
21
22		Total c/c	cleared	O/s
23	12/31/2017	12,035.54		12,035.54
24	Jan-18 c-card	1,596.00	798.00	798.00
25		13,631.54	798.00	12,833.54
Sheet1

Cell Formulas
Range	Formula
C20	=SUM(C3:C19)
F23	=D23-E23
F24	=D24-E24
F25	=SUM(F23:F24)
D23	=SUM(SUMIF($D$3:$D$19,{"Oct-17 c-card","Nov-17 c-card","Dec-17 c-card"},$C$3:$C$19))+1250
D24	=SUM(SUMIF($D$3:$D$19,C24,$C$3:$C$19))
D25	=SUM(D23:D24)
E24	=SUMIFS($C$1:$C$17,D1:D17,C24,$E$1:$E$17,"Cleared")
E25	=SUM(E23:E24)

In E23 required value should be 4,550.21 (294.49+111.87+394.96+406.62+1445+1897.27)

Could you suggest formula please? (maybe one similar to formula in D23)

 

[Special-K99]

Pls ignore this

 

[tyija1995]

Hey,

Try this in E23:
SUMIFS(C3:C19,E3:E19,"cleared",D3:D19,"Oct-17 c-card")+SUMIFS(C3:C19,E3:E19,"cleared",D3:D19,"Nov-17 c-card")+SUMIFS(C3:C19,E3:E19,"cleared",D3:D19,"Dec-17 c-card")

 

[Sean15]

Hello:

Thanks for suggestion.

My data sample above only shows three months. My actual data has several months so if I use formula shown in post#3, it would a long formula. Is there a shorter version - maybe a formula similar to the one in D23?

Regards,

Sean

 

[Special-K99]

1. Data incosistency: Change A23 to "Dec 2017 c-card"

2. Then in E23
=SUMPRODUCT((A3:A21)*(D3:D21="cleared")*(B3:B21=A23))
and copy down the column