Sumifs with Date Criteria

[ChetanPuri]

Hello Excel Gurus,

I have a question , I have three companies 2 based on Quarterly reporting and one based on monthly reporting, I am trying to create a Sumifs formula based on criteria, you will see in Rows D1, H1 and L1, I got Quarterly or Monthly in it and then right I got Q2, R 2 and S 2 , I want a formula to calculate the sum from sheet 2 based on the dates but if it says quartely then it sums all the data between the quartely dates and if it says monthly then does it for month only. Any help wopuld be appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	CODES	Costing Work ID	60	Quaterly			21	Quaterly			50	Monthly				Year	Start Date	Monthly	Quarterly
2	GST Codes	Transaction Type	Ex GST	GST Amount	GST Inclusive		Ex GST	GST Amount	GST Inclusive		Ex GST	GST Amount	GST Inclusive			2024	01-Jul-24	31-Jul-24	30-Sep-24
3	A0	AP INV	2,673.63	0.00	2,673.63		7,261.09	0.00	7,261.09		474,182.04	0.00	474,182.04
4	A1	AP INV	309,929.03	30,992.89	340,921.92		2,523,349.49	252,335.04	2,775,684.53		2,668,871.17	266,887.45	2,935,758.62
5	A1	AP CRD	(2,931.82)	(293.18)	(3,225.00)		(1,389.41)	(138.92)	(1,528.33)		(28,644.41)	(2,864.48)	(31,508.89)
6	A1	DR DBT	0.00	0.00	0.00						(6,519.65)	6,519.65	(0.00)
7	A2	AP PPI	872.00	0.00	872.00		0.00	0.00	0.00		0.00	0.00	0.00
8	A2	AP INV	246.30	0.00	246.30		17,602.75	0.00	17,602.75		175,927.89	0.00	175,927.89
9	A2	AP CRD	0.00	0.00	0.00		(719.00)	0.00	(719.00)		(5,318.72)	0.00	(5,318.72)
10	A4	AP INV	102.00	0.00	102.00		159.50	0.00	159.50		0.00	0.00	0.00
11	A5	AP INV	0.00	0.00	0.00		74,000.00	7,400.00	81,400.00		0.00	0.00	0.00
12	Total as per GL		310,891.14	30,699.71	341,590.85		2,620,264.42	259,596.12	2,879,860.54		3,278,498.32	270,542.62	3,549,040.94
Sheet1

Cell Formulas
Range	Formula
R2	R2	=IF(R1="Quarterly",EOMONTH(Q2,ROUNDUP(MONTH(Q2)/3,0)*3-MONTH(Q2)),IF(R1="Monthly",EOMONTH(Q2,0),"Invalid input"))
S2	S2	=IF(S1="Quarterly",EOMONTH(Q2,ROUNDUP(MONTH(Q2)/3,0)*3-MONTH(Q2)),IF(S1="Monthly",EOMONTH(Q2,0),"Invalid input"))

Book1
	H	I	J	K	L	M	N	O	P
1	Journal Date	Transaction Type	Cred/Debt No.	Tax Code	Internal Reference	Creditor/Debtor Name	GL Description	Costing Work Id	Transaction Amount
2	15-08-2024	AP INV	BUNNIN	A1	2001267775			60	111.02
3	04-09-2024	AP INV	STABLI	A1	00024460			60	96.82
4	01-07-2024	AP INV	HALSER	A1	940006572			60	175,645.17
5	16-06-2024	AP INV	WEBSTER	A1	154942			60	1,510.00
6	16-07-2024	AP INV	KPMG	A0	821633110			60	1,720.40
Sheet2

 

[bebo021999]

Could you mock up some expected results manually?

 

[Peter_SSs]

You have not said where this formula is to go or just what conditions apply to the SUMIFS but see it this helps get you where you want to go.
I have added formulas in E1, I1 and M1 to add the figures from column P of Sheet2 where the Costing Work ID in C1, G1 and K1 match those in Sheet2 column O and the dates in Sheet2 column H are in the relevant range.

I have added some more sample data to Sheet2

ChetanPuri.xlsm
	H							O	P
1	Journal Date							Costing Work Id	Transaction Amount
2	15-08-2024							60	111.02
3	04-09-2024							60	96.82
4	01-07-2024							60	175,645.17
5	16-06-2024							60	1,510.00
6	16-07-2024							60	1,720.40
7	15-08-2024							21	1,929.00
8	04-09-2024							50	1,752.00
9	01-07-2024							21	4,313.00
10	16-06-2024							50	3,847.00
11	16-07-2024							50	923.00
12	15-08-2024							60	2,175.00
13	04-09-2024							21	2,334.00
14	01-07-2024							50	604.00
15	16-06-2024							21	2,315.00
16	16-07-2024							50	469.00
17	15-08-2024							60	4,151.00
18	04-09-2024							21	1,509.00
19	01-07-2024							50	1,278.00
20	16-06-2024							60	2,890.00
Sheet2

ChetanPuri.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	CODES	Costing Work ID	60	Quarterly	183899.41		21	Quarterly	10085		50	Monthly	3274			Year	Start Date	Monthly	Quarterly
2	GST Codes	Transaction Type	Ex GST	GST Amount	GST Inclusive		Ex GST	GST Amount	GST Inclusive		Ex GST	GST Amount	GST Inclusive			2024	01-Jul-24	31-Jul-24	30-Sep-24
Sheet1

Cell Formulas
Range	Formula
E1,M1,I1	E1	=SUMIFS(Sheet2!$P2:$P100,Sheet2!$O2:$O100,C1,Sheet2!$H2:$H100,">="&$Q2,Sheet2!$H2:$H100,"<="&IF(D1="Quarterly",$S2,$R2))
R2	R2	=IF(R1="Quarterly",EOMONTH(Q2,ROUNDUP(MONTH(Q2)/3,0)*3-MONTH(Q2)),IF(R1="Monthly",EOMONTH(Q2,0),"Invalid input"))
S2	S2	=IF(S1="Quarterly",EOMONTH(Q2,ROUNDUP(MONTH(Q2)/3,0)*3-MONTH(Q2)),IF(S1="Monthly",EOMONTH(Q2,0),"Invalid input"))