Sumifs based on dates and criteria

mba_110

Board Regular
Joined
Nov 28, 2012
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,
Sorry to bother you my new laptop don't have XL2BB, hence i am putting this screenshot.



C8G8H8I8J8K8L8M8N8O8P8Q8R8
S.NOCLINIC NAME JAN 2024 FEB 2024 MAR 2024 APR 2024 MAY 2024 JUN 2024 JUL 2024 AUG 2024 SEP 2024 OCT 2024 NOV 2024 DEC 2024
A111CA
A122RA
A133CC
A144AA
A155DD
A166BA
A177
A188
A199
A2010
A2111
A2212
A2313
TOTAL AMOUNT : - - - - - - - - - - - -
DEPOSIT DETAILS
S.NOPAYMENT METHODCHEQUE DATECHEQUE NOCLINIC NAME AMOUNT DEPOSIT DATEBANKSTATUS
A341Cheque21 Jan 2024500081CA 8,822.4527/02/2024
A352Cheque21 Jan 2024345RA 15,133.1227/02/2024
A363Cheque21 Jan 20243CC 11,792.5520/02/2024
A374Cheque21 Jan 202416AA 23,103.6827/02/2024
A385Cheque21 Jan 2024500311DD 15,312.5027/02/2024
A396Cheque21 Jan 202468CC 57,170.0020/02/2024
A407Cheque21 Jan 202441BA 10,937.5027/02/2024
A418Cheque21 Jan 20241098CA 27,825.5227/02/2024
A429Cheque21 Jan 2024405RA 30,710.7527/02/2024
A4310Cheque21 Feb 2024500082CC 8,822.4520/02/2024
A4411Cheque21 Feb 2024346AA 15,133.1220/02/2024
A4512Cheque21 Feb 2024500116DD 11,511.5020/02/2024
A4613Cheque21 Feb 20241070CC 35,869.5020/02/2024
A4714Cheque21 Feb 202417BA 23,103.6820/02/2024
A4815Cheque21 Feb 202469CA 57,170.0020/02/2024
A4916Cheque21 Feb 20241093RA 10,500.0020/02/2024
A5017Cheque21 Feb 202440CC 10,937.5020/02/2024
A5118Cheque21 Feb 202474AA 8,531.2520/02/2024
A5219Cheque21 Feb 20241099DD 27,825.5220/02/2024
A5320Cheque21 Feb 2024404CC 7,218.7520/02/2024
A5421Cheque21 Feb 2024406BA 30,710.7520/02/2024
A5522Cheque21 Feb 202444CA 7,796.2520/02/2024
A5623Cheque21 Feb 2024432RA 13,283.0020/02/2024
A5724Cheque21 Feb 2024110CC 11,792.5820/02/2024
A5825Cheque22 Feb 202476AA 37,201.5020/02/2024
A5926Cheque21 Mar 202470DD 57,170.0007/04/2024
A6027Cheque21 Mar 2024433CC 13,283.0021/03/2024
A6128Cheque21 Apr 2024500309BA 75,882.9203/06/2024
A6229Cheque21 Apr 202471CA 57,170.0015/05/2024
A6330Cheque21 Apr 20241101RA 27,825.5415/05/2024
A6431Cheque21 Apr 2024500468CC 19,003.2515/05/2024
A6532Cheque21 Apr 2024500017AA 7,057.0515/05/2024
A6633Cheque21 Apr 2024500139DD 17,739.7515/05/2024
A6734Cheque21 Apr 2024182CC 8,580.2515/05/2024
A6835Cheque21 May 2024500310AA 75,882.9225/06/2024
A6936Cheque21 May 2024436DD 52,084.2025/06/2024
A7037Cheque21 May 2024435CC 13,283.1025/06/2024
A7138Cheque21 May 2024139AA 36,052.0125/06/2024
A7239Cheque21 May 2024145DD 53,340.8725/06/2024
A7340Cheque21 May 20241171CC 18,699.7225/06/2024
A7441Cheque21 May 2024116AA 49,359.8025/06/2024


Based on below i am trying to calculate the total value of each month Jan 2024 and more, the criteria should be the Clinic name which start from A11.

I achieved the total by clinic name with this formula - "=SUMIF($F$34:$F$57,C11,$H$34:$H$57)"

But now i need to include dates condition also to fill each month column for every clinic.
 
I don't understand in your sample data G11 is showing $ 140,215.50 which agrees to the transaction table, where are you getting your 2 numbers from ?
In your sample data the only column that doesn't agree is February.

1735558591106.png
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I don't understand in your sample data G11 is showing $ 140,215.50 which agrees to the transaction table, where are you getting your 2 numbers from ?
In your sample data the only column that doesn't agree is February.

View attachment 120743
Here is the data i am referring to the calculation.

Seven D Medical Clinics - Report.xlsx
CDEFGHIJKLMNOPQRS
8
9CLINIC NAMEJAN 2024FEB 2024MAR 2024APR 2024MAY 2024JUN 2024JUL 2024AUG 2024SEP 2024OCT 2024NOV 2024DEC 2024 TOTAL AMOUNT
10
11Derma Dent Medical Center LLC18,732.00105,035.0134,041.16--101,422.6465,370.63-65,370.6429,318.6426,910.1040,120.08486,320.90
12Ageless Medical Center73,318.0943,549.2413,283.0013,283.007,057.0584,598.0429,802.67-47,469.4520,071.2643,885.78-376,317.58
13Seven D Medical Centre528,033.9130,625.00114,503.70128,077.92-305,149.45124,496.79-55,857.87157,378.85324,026.55-1,768,150.04
14OneLife Medical Center Medical Complex LLC45,732.75183,657.8886,561.30106,529.8057,170.00194,865.2892,282.3319,411.88165,152.7853,340.90230,047.36-1,234,752.26
15Seven Dental Center LLC83,768.92102,020.5463,695.0254,569.2246,828.79166,761.2783,195.35-37,332.4037,332.4742,776.65-718,280.63
16Heaven Medical Center111,387.1568,640.2530,710.7552,084.2017,739.75163,467.5169,009.25-121,737.6397,103.32134,707.14-866,586.95
17New Age Clinic LLC----8,580.2517,431.768,851.7620,718.9529,570.7155,370.0625,799.3420,718.95187,041.78
18Mermaid Medical Center-----20,936.5220,936.5220,936.52-20,936.5241,873.04-125,619.12
19Well Touch Medical Center------26,325.1622,749.5487,999.8655,374.69-29,049.55221,498.80
20Triple Seven Medical Center L.L.C---------4,456.5425,999.38-30,455.92
21MD Prime---------66,438.45132,876.90-199,315.35
22Cocoona Aesthetic Transformation Center L.L.C - Alwasal----------60,729.38-60,729.38
23Royal Glow Medical Center FZ-LLC----------6,729.45-6,729.45
24
25
26TOTAL AMOUNT :860,972.82533,527.92342,794.93354,544.14137,375.841,054,632.47520,270.4683,816.89610,491.34597,121.701,096,361.0789,888.586,281,798.16
27
28
29
30
31
32
33PAYMENT METHODCHEQUE DATECHEQUE NOCLINIC NAMEAMOUNTDEPOSIT DATEBANKSTATUS
34
35Cheque21 Jan 2024500081Derma Dent Medical Center LLC8,822.4527 Feb 2024
36Cheque21 Jan 2024500086Derma Dent Medical Center LLC10,935.7521 Jan 2024
37Cheque21 Jan 2024310Ageless Medical Center4,168.5021 Jan 2024
38Cheque21 Jan 2024500160Seven D Medical Centre13,388.3921 Jan 2024
39Cheque21 Jan 2024500206Seven D Medical Centre15,363.2521 Jan 2024
40Cheque21 Jan 2024345Ageless Medical Center15,133.1227 Feb 2024
41Cheque21 Jan 20243OneLife Medical Center Medical Complex LLC11,792.5520 Feb 2024
42Cheque21 Jan 20241069Seven Dental Center LLC35,869.5021 Jan 2024
43Cheque21 Jan 202416Derma Dent Medical Center LLC23,103.6827 Feb 2024
44Cheque21 Jan 2024381Ageless Medical Center23,049.8621 Jan 2024
45Cheque21 Jan 2024500311Seven D Medical Centre15,312.5027 Feb 2024
46Cheque21 Jan 2024500325Seven D Medical Centre36,047.1021 Jan 2024
47Cheque21 Jan 202468OneLife Medical Center Medical Complex LLC57,170.0020 Feb 2024
48Cheque21 Jan 2024500325Seven D Medical Centre36,047.1021 Jan 2024
49Cheque21 Jan 20241092Seven Dental Center LLC10,500.0021 Jan 2024
50Cheque21 Jan 202441Derma Dent Medical Center LLC10,937.5027 Feb 2024
51Cheque21 Jan 202473OneLife Medical Center Medical Complex LLC8,531.2521 Jan 2024
52Cheque21 Jan 202475OneLife Medical Center Medical Complex LLC37,201.5021 Jan 2024
53Cheque21 Jan 20241098Seven Dental Center LLC27,825.5227 Feb 2024
54Cheque21 Jan 2024403Heaven Medical Center7,218.7521 Jan 2024
55Cheque21 Jan 2024405Heaven Medical Center30,710.7527 Feb 2024
56Cheque21 Jan 202443Derma Dent Medical Center LLC7,796.2521 Jan 2024
57Cheque27 Jan 2024500306Seven D Medical Centre75,882.9227 Jan 2024
58Cheque30 Jan 20241233Seven D Medical Centre269,486.3530 Jan 2024
59Cheque30 Jan 20241234Seven Dental Center LLC37,399.4230 Jan 2024
60Cheque30 Jan 20241234Ageless Medical Center46,099.7330 Jan 2024
61Cheque30 Jan 20241234Heaven Medical Center104,168.4030 Jan 2024
62Cheque30 Jan 20241234Seven D Medical Centre81,818.8030 Jan 2024
63Cheque21 Feb 2024500082Derma Dent Medical Center LLC8,822.4520 Feb 2024
64Cheque21 Feb 2024346Ageless Medical Center15,133.1220 Feb 2024
65Cheque21 Feb 2024500116Derma Dent Medical Center LLC11,511.5020 Feb 2024
66Cheque21 Feb 20241070Seven Dental Center LLC35,869.5020 Feb 2024
67Cheque21 Feb 202417Derma Dent Medical Center LLC23,103.6820 Feb 2024
68Cheque21 Feb 2024383Ageless Medical Center21 Feb 2024
69Cheque21 Feb 2024500312Seven D Medical Centre15,312.5021 Feb 2024
70Cheque21 Feb 2024500326Seven D Medical Centre21 Feb 2024
71Cheque21 Feb 2024500307Seven D Medical Centre21 Feb 2024
72Cheque21 Feb 202469OneLife Medical Center Medical Complex LLC57,170.0020 Feb 2024
73Cheque21 Feb 2024500326Seven D Medical Centre21 Feb 2024
74Cheque21 Feb 20241093Seven Dental Center LLC10,500.0020 Feb 2024
75Cheque21 Feb 202440Derma Dent Medical Center LLC10,937.5020 Feb 2024
76Cheque21 Feb 202474OneLife Medical Center Medical Complex LLC8,531.2520 Feb 2024
77Cheque21 Feb 20241099Seven Dental Center LLC27,825.5220 Feb 2024
78Cheque21 Feb 2024404Heaven Medical Center7,218.7520 Feb 2024
79Cheque21 Feb 2024406Heaven Medical Center30,710.7520 Feb 2024
80Cheque21 Feb 202444Derma Dent Medical Center LLC7,796.2520 Feb 2024
81Cheque21 Feb 2024500378Seven D Medical Centre21 Feb 2024
82Cheque21 Feb 2024433Heaven Medical Center21 Feb 2024
83Cheque21 Feb 2024432Ageless Medical Center13,283.0020 Feb 2024
84Cheque21 Feb 2024110OneLife Medical Center Medical Complex LLC11,792.5820 Feb 2024
85Cheque21 Feb 20241168Seven Dental Center LLC21 Feb 2024
86Cheque22 Feb 202476OneLife Medical Center Medical Complex LLC37,201.5020 Feb 2024
87Cheque21 Mar 20241071Seven Dental Center LLC35,869.5021 Mar 2024
88Cheque21 Mar 202418Derma Dent Medical Center LLC23,103.6621 Mar 2024
89Cheque21 Mar 2024384Ageless Medical Center21 Mar 2024
90Cheque21 Mar 2024500327Seven D Medical Centre36,047.1021 Mar 2024
91Cheque21 Mar 2024500313Seven D Medical Centre15,312.5021 Mar 2024
92Cheque21 Mar 2024500308Seven D Medical Centre21 Mar 2024
93Cheque21 Mar 202470OneLife Medical Center Medical Complex LLC57,170.007 Apr 2024
94Cheque21 Mar 2024500327Seven D Medical Centre36,047.1021 Mar 2024
95Cheque21 Mar 202442Derma Dent Medical Center LLC10,937.5021 Mar 2024
Cheque Deposits - 2024
Cell Formulas
RangeFormula
C11:C23C11=UNIQUE($F$35:$F$265)
G11G11=SUMIFS($H$35:$H$265,$I$35:$I$265,">="&$G$9,$I$35:$I$265,"<="&EOMONTH(G$9,0),$F$35:$F$265,C11)
H11:H23H11=SUMIFS($H$35:$H$265,$I$35:$I$265,">="&H$9,$I$35:$I$265,"<="&EOMONTH(H$9,0),$F$35:$F$265,C11)
I11:I23I11=SUMIFS($H$35:$H$265,$I$35:$I$265,">="&I$9,$I$35:$I$265,"<="&EOMONTH(I$9,0),$F$35:$F$265,C11)
J11:J23J11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$J$9,$I$35:$I$269,"<="&EOMONTH($J$9,0),$F$35:$F$269,C11)
K11:K23K11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$K$9,$I$35:$I$269,"<="&EOMONTH($K$9,0),$F$35:$F$269,C11)
L11:L23L11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$L$9,$I$35:$I$269,"<="&EOMONTH($L$9,0),$F$35:$F$269,C11)
M11:M23M11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$M$9,$I$35:$I$269,"<="&EOMONTH($M$9,0),$F$35:$F$269,C11)
N11:N23N11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$N$9,$I$35:$I$269,"<="&EOMONTH($N$9,0),$F$35:$F$269,C11)
O11:O23O11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$O$9,$I$35:$I$269,"<="&EOMONTH($O$9,0),$F$35:$F$269,C11)
P11:P23P11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$P$9,$I$35:$I$269,"<="&EOMONTH($P$9,0),$F$35:$F$269,C11)
Q11:Q23Q11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$Q$9,$I$35:$I$269,"<="&EOMONTH($Q$9,0),$F$35:$F$269,C11)
R11:R23R11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$R$9,$I$35:$I$269,"<="&EOMONTH($R$9,0),$F$35:$F$269,C11)
S11:S23S11=SUBTOTAL(9,G11:R11)
G12:G23G12=SUMIFS($H$35:$H$265,$I$35:$I$265,">="&G$9,$I$35:$I$265,"<="&EOMONTH(G$9,0),$F$35:$F$265,C12)
G26:S26G26=SUM(G11:G25)
Dynamic array formulas.
 
Upvote 0
The Formula for JAN 2024 is not giving correct result no need to go further, i check manually the G11 should have total of 10,935.75 but its showing total of 18,732 which is wrong
Why do you think it should total $ 10,935.75 ?

1735560953805.png


Note: I ran a pivot over the transaction data and grouped the dates by month. It the summary to Pivot match up looked fine to me.
 
Upvote 0

Forum statistics

Threads
1,225,228
Messages
6,183,710
Members
453,183
Latest member
Walshy10

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