Sumifs based on dates and criteria

M

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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Yeah thanks for your reply, but i want to go with formula.
 
Upvote 0
Then see option 1.
If you have a specific question about it, please detail it.
 
Upvote 0
Its working fine with below formula.

"=SUMIFS($H$34:$H$268,$D$34:$D$268,">="&G9,$D$34:$D$268,"<="&EOMONTH(G9,0),$F$34:$F$268,C11)"
 
Last edited:
Upvote 0
The result of total is not coming correct, can you please check what is wrong in this formula.
 
Upvote 0
You are giving us not visibility of what "not coming correct" means.
If it is only not correct when you copy it down and across try the below which adds absolute references.
Rich (BB code): 
=SUMIFS($H$34:$H$268, $D$34:$D$268,">="&G$9, $D$34:$D$268,"<="&EOMONTH(G$9,0), $F$34:$F$268,$C11)

Your sample data is not lining up with your formula so we can only assume that column H is the Amount column (the way it lines up when I copy it in H is the Date column)

If the above doesn't fix it then it is most likely a date issue. To check that
• select row 9 and change the formatting to General. Do all the dates change into a number ? (ctrl+z to reset the formatting)
• if the above worked select column D from row 34 down change the formatting to General. Again do all the dates change into a number ? (ctrl+z to reset the formatting)
• are the dates in row 9 all the 1st of the month ?
 
Upvote 0
Sorry to bother you now i got the laptop fixed, here is the XL2BB for this formulas.

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
11AA140,215.5045,321.0035,869.5018,699.71-94,930.6315,710.10-65,821.6075,184.0836,085.00-527,837.12
12AB103,254.5545,321.0036,386.6649,359.80-53,827.1926,635.19-78,031.6213,608.5481,966.31-488,390.86
13AC4,168.50--28,886.72-66,204.07109,218.58-35,438.394,456.5426,131.9820,718.95295,223.73
14DC21,919.64-85,406.90-19,003.2574,749.6755,581.5922,749.5417,739.75105,486.6226,971.146,300.00435,908.10
15GE52,564.75-15,312.5035,869.517,057.0547,889.9771,670.0219,411.8826,325.16106,637.1940,862.90-423,600.93
16RT---36,047.1017,739.7526,060.3034,095.8620,718.9515,710.1015,761.55132,087.27-298,220.88
17YU7,218.75--57,170.008,580.25100,679.7241,277.64-32,935.1920,936.5241,287.49-310,085.56
18HJ35,869.50-36,047.10-57,170.00113,034.5454,714.71-66,949.39101,520.98160,994.16-626,300.38
19DW7,796.25-10,937.5036,047.10-76,374.3055,211.89-19,529.5839,496.4466,277.8922,749.55334,420.50
20YT98,932.7845,318.0037,201.50-27,825.5459,330.1619,003.25-54,381.1771,731.62163,288.36-577,012.38
21JU269,486.3545,321.0027,825.5227,097.00-152,327.75--80,312.0126,591.52177,300.18-806,261.33
22TR73,446.5245,342.0030,710.7552,084.20-107,722.0317,739.75-76,257.29-17,339.1640,120.08460,761.78
23IU46,099.7390,663.0027,097.0013,283.00-81,502.1419,411.8820,936.5241,060.0915,710.10125,769.23-481,532.69
24
25
26TOTAL AMOUNT :860,972.82317,286.00342,794.93354,544.14137,375.841,054,632.47520,270.4683,816.89610,491.34597,121.701,096,361.0789,888.586,065,556.24
27
28
29
30
31
32
33PAYMENT METHODCHEQUE DATECHEQUE NOCLINIC NAME AMOUNT DEPOSIT DATEBANKSTATUS
34
35Cheque21 Jan 2024500081AA8,822.4527 Feb 2024
36Cheque21 Jan 2024500086AB10,935.7521 Jan 2024
37Cheque21 Jan 2024310AC4,168.5021 Jan 2024
38Cheque21 Jan 2024500160DC13,388.3921 Jan 2024
39Cheque21 Jan 2024500206GE15,363.2521 Jan 2024
40Cheque21 Jan 2024345RT15,133.1227 Feb 2024
41Cheque21 Jan 20243YU11,792.5520 Feb 2024
42Cheque21 Jan 20241069HJ35,869.5021 Jan 2024
43Cheque21 Jan 202416DW23,103.6827 Feb 2024
44Cheque21 Jan 2024381YT23,049.8621 Jan 2024
45Cheque21 Jan 2024500311JU15,312.5027 Feb 2024
46Cheque21 Jan 2024500325TR36,047.1021 Jan 2024
47Cheque21 Jan 202468IU57,170.0020 Feb 2024
48Cheque21 Jan 2024500325AA36,047.1021 Jan 2024
49Cheque21 Jan 20241092AB10,500.0021 Jan 2024
50Cheque21 Jan 202441AC10,937.5027 Feb 2024
51Cheque21 Jan 202473DC8,531.2521 Jan 2024
52Cheque21 Jan 202475GE37,201.5021 Jan 2024
53Cheque21 Jan 20241098RT27,825.5227 Feb 2024
54Cheque21 Jan 2024403YU7,218.7521 Jan 2024
55Cheque21 Jan 2024405HJ30,710.7527 Feb 2024
56Cheque21 Jan 202443DW7,796.2521 Jan 2024
57Cheque27 Jan 2024500306YT75,882.9227 Jan 2024
58Cheque30 Jan 20241233JU269,486.3530 Jan 2024
59Cheque30 Jan 20241234TR37,399.4230 Jan 2024
60Cheque30 Jan 20241234IU46,099.7330 Jan 2024
61Cheque30 Jan 20241234AA104,168.4030 Jan 2024
62Cheque30 Jan 20241234AB81,818.8030 Jan 2024
63Cheque21 Feb 2024500082AC8,822.4520 Feb 2024
64Cheque21 Feb 2024346DC15,133.1220 Feb 2024
65Cheque21 Feb 2024500116GE11,511.5020 Feb 2024
66Cheque21 Feb 20241070RT35,869.5020 Feb 2024
67Cheque21 Feb 202417YU23,103.6820 Feb 2024
68Cheque21 Feb 2024383HJ-21 Feb 2024
69Cheque21 Feb 2024500312DW15,312.5021 Feb 2024
70Cheque21 Feb 2024500326YT-21 Feb 2024
71Cheque21 Feb 2024500307JU-21 Feb 2024
72Cheque21 Feb 202469TR57,170.0020 Feb 2024
73Cheque21 Feb 2024500326IU-21 Feb 2024
74Cheque21 Feb 20241093AA10,500.0020 Feb 2024
75Cheque21 Feb 202440AB10,937.5020 Feb 2024
76Cheque21 Feb 202474AC8,531.2520 Feb 2024
77Cheque21 Feb 20241099DC27,825.5220 Feb 2024
78Cheque21 Feb 2024404GE7,218.7520 Feb 2024
79Cheque21 Feb 2024406RT30,710.7520 Feb 2024
80Cheque21 Feb 202444YU7,796.2520 Feb 2024
81Cheque21 Feb 2024500378HJ-21 Feb 2024
82Cheque21 Feb 2024433DW-21 Feb 2024
83Cheque21 Feb 2024432YT13,283.0020 Feb 2024
84Cheque21 Feb 2024110JU11,792.5820 Feb 2024
85Cheque21 Feb 20241168TR-21 Feb 2024
86Cheque22 Feb 202476IU37,201.5020 Feb 2024
87Cheque21 Mar 20241071AA35,869.5021 Mar 2024
88Cheque21 Mar 202418AB23,103.6621 Mar 2024
89Cheque21 Mar 2024384AC-21 Mar 2024
90Cheque21 Mar 2024500327DC36,047.1021 Mar 2024
91Cheque21 Mar 2024500313GE15,312.5021 Mar 2024
92Cheque21 Mar 2024500308RT-21 Mar 2024
93Cheque21 Mar 202470YU57,170.007 Apr 2024
Cheque Deposits - 2024
Cell Formulas
RangeFormula
C11:C23C11=UNIQUE($F$35:$F$265)
G11:G23G11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&G$9,$I$35:$I$269,"<="&EOMONTH($G$9,0),$F$35:$F$269,$C11)
H11:H23H11=SUMIFS($I$35:$I$269,$H$35:$H$269,">="&H$9,$I$35:$I$269,"<="&EOMONTH($H$9,0),$F$35:$F$269,$C11)
I11:I23I11=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&I$9,$I$35:$I$269,"<="&EOMONTH($I$9,0),$F$35:$F$269,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)
K11K11=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)
K12:K23K12=SUMIFS($H$35:$H$269,$I$35:$I$269,">="&$K$9,$I$35:$I$269,"<="&EOMONTH($K$9,0),$F$35:$F$269,C12)
G26:S26G26=SUM(G11:G25)
Dynamic array formulas.


are the dates in row 9 all the 1st of the month ?
formatting is general and mentioned 'JAN-2024

The total for all the months are not not matching with the overall total, i don't know what is wrong in this formula.
 
Upvote 0
mba_110 said:
The total for all the months are not not matching with the overall total, i don't know what is wrong in this formula.
Click to expand...

Your formula for Feb 2024 is incorrect. Compare it against the formula in all the other columns, you will find it is summing the Date column.

What I would recommend is that you fix the formula the first cell in January 2024 G11 by removing the "$" in front of the G changing it from EOMONTH($G$9,0) to EOMONTH(G$9,0).
Then copy it down all the rows in column Jan 24 and then copy Jan 24 across to all months.
 
Upvote 0
Alex Blakenburg said:
Your formula for Feb 2024 is incorrect. Compare it against the formula in all the other columns, you will find it is summing the Date column.

What I would recommend is that you fix the formula the first cell in January 2024 G11 by removing the "$" in front of the G changing it from EOMONTH($G$9,0) to EOMONTH(G$9,0).
Then copy it down all the rows in column Jan 24 and then copy Jan 24 across to all months.
Click to expand...
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 at the first point so, no use to copy the wrong result formula to other columns.

I explain the same in above XL2BB you can go through manually also, the above figures for JAN 2024 is not matching by formula. regarding $ sign it makes no difference to formula weather i keep it or remove it.
I don't know what is the issue with this formula.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
213
RobP
R
A
  • Solved
Extract last amounts in column within two dates
Replies
7
Views
159
Peter_SSs
Peter_SSs
B
  • Question Question
Percent Change
Replies
4
Views
216
kvsrinivasamurthy
K
K
  • Question Question
Copy data from rows with the dates (Monday to Sunday)
Replies
1
Views
86
K_Stevs1
K
O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
223
Omran Y
O

Forum statistics

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

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