Sum and Count functions

WESTERNWALL

WESTERNWALL

Board Regular
Joined
Oct 8, 2002
Messages
204
Office Version
  1. 365
Platform
  1. MacOS
Hi Y'all
Appreciate your assistance with sum and count functions in a large list of donations. I've attached the file. It's not the entire file. I have set up a table next to the list and should be self-explanatory.
Many thanks
Example for MrExcel 1.xlsx
ABCDEF
1Donation DateOriginal Donation
210/2/23$ 50.00MonthNo. of DonationsTotal Received
310/2/23$ 18.00Oct 2023
410/3/23$ 2,000.00Nov 2023
510/3/23$ 200.00Dec 2023
610/3/23$ 1,000.00Jan 2024
710/3/23$ 500.00Feb 2024
810/4/23$ 200.00Mar 2024
910/4/23$ 10,000.00Apr 2024
1010/4/23$ 18.00
1110/7/23$ 100.00
1210/7/23$ 100.00Gift AmountNo. of DonationsTotal Amount
1310/8/23$ 263.00Above $100,000$ 100,000
1410/8/23$ 500.00from $50,000 to $99,999$ 50,000
1510/8/23$ 100.00from $10,000 to $49,999$ 10,000
1610/8/23$ 180.00from $5,000 to $9,999$ 5,000
1710/8/23$ 118.00from $1,000 to $4,999$ 1,000
1810/8/23$ 36.00
1910/8/23$ 100.00
2010/8/23$ 30.00
2110/8/23$ 100.00
2210/8/23$ 300.00
2310/8/23$ 540.00
2410/8/23$ 251.00
2510/8/23$ 500.00
2610/9/23$ 260.00
2710/9/23$ 500.00
2810/9/23$ 1,000.00
2910/9/23$ 1,000.00
3010/9/23$ 200.00
3110/9/23$ 50.00
3210/9/23$ 150.00
3310/9/23$ 400.00
3410/9/23$ 250.00
3510/9/23$ 72.00
3610/9/23$ 50.00
3710/9/23$ 75.00
3810/9/23$ 50.00
3910/9/23$ 100.00
4010/9/23$ 500.00
4110/9/23$ 80.00
4210/9/23$ 50.00
4310/9/23$ 50.00
4410/9/23$ 180.00
4510/9/23$ 25.00
4610/9/23$ 300.00
4710/9/23$ 180.00
4810/9/23$ 200.00
4910/9/23$ 250.00
5010/9/23$ 180.00
5110/9/23$ 465.00
5210/9/23$ 500.00
5310/9/23$ 350.00
5410/9/23$ 250.00
5510/9/23$ 500.00
5610/9/23$ 54.00
5710/9/23$ 250.00
5810/9/23$ 48.00
5910/9/23$ 250.00
6010/9/23$ 100.00
6110/9/23$ 500.00
6210/9/23$ 150.00
6310/9/23$ 20.00
6410/9/23$ 118.00
6510/9/23$ 50.00
6610/9/23$ 10.00
6710/9/23$ 100.00
6810/9/23$ 100.00
6910/9/23$ 36.00
7010/9/23$ 200.00
7110/9/23$ 18.00
7210/9/23$ 5,000.00
7310/9/23$ 35.00
7410/9/23$ 18.00
7510/9/23$ 77.00
7610/9/23$ 50.00
7710/9/23$ 150.00
7810/9/23$ 500.00
7910/9/23$ 250.00
8010/9/23$ 50.00
8110/9/23$ 26.00
8210/9/23$ 300.00
8310/9/23$ 100.00
8410/9/23$ 100.18
8510/9/23$ 30.00
8610/9/23$ 250.00
8710/9/23$ 100.00
8810/9/23$ 250.00
8910/9/23$ 1,000.00
9010/9/23$ 70.00
9110/9/23$ 36.00
9210/9/23$ 500.00
9310/9/23$ 100.00
9410/9/23$ 360.00
9510/9/23$ 180.00
9610/9/23$ 100.00
9710/9/23$ 180.00
9810/9/23$ 1,000.00
9910/9/23$ 1,000.00
10010/9/23$ 80.00
10110/9/23$ 180.00
10210/9/23$ 1,000.00
10310/9/23$ 100.00
10410/9/23$ 180.00
10510/9/23$ 500.00
10610/9/23$ 36.00
10710/9/23$ 100.00
10810/9/23$ 30.00
10910/9/23$ 36.00
11010/9/23$ 150.00
11110/9/23$ 1,000.00
11210/9/23$ 25.00
11310/9/23$ 200.00
11410/9/23$ 126.00
11510/9/23$ 100.00
11610/9/23$ 180.00
11710/9/23$ 3,600.00
11810/9/23$ 260.00
11910/9/23$ 500.00
12010/9/23$ 1,000.00
12110/9/23$ 200.00
12210/9/23$ 20.00
12310/9/23$ 1,000.00
12410/9/23$ 100.00
12510/9/23$ 100.00
12610/9/23$ 500.00
12710/9/23$ 500.00
12810/9/23$ 360.00
12910/9/23$ 360.00
13010/9/23$ 5,000.00
13110/9/23$ 500.00
13210/9/23$ 200.00
13310/9/23$ 20.00
13410/9/23$ 200.00
13510/9/23$ 200.00
13610/9/23$ 200.00
13710/9/23$ 180.00
13810/9/23$ 52.00
13910/9/23$ 333.00
14010/9/23$ 260.00
14110/9/23$ 126.00
14210/9/23$ 100.00
14310/9/23$ 360.00
14410/9/23$ 100.00
14510/9/23$ 500.00
14610/9/23$ 252.00
14710/9/23$ 250.00
14810/9/23$ 75.00
14910/9/23$ 300.00
15010/9/23$ 5,000.00
15110/9/23$ 250.00
15210/9/23$ 100.00
15310/9/23$ 25.00
15410/9/23$ 5,000.00
15510/9/23$ 126.00
15610/9/23$ 180.00
15710/9/23$ 20.00
15810/9/23$ 100.00
15910/9/23$ 20.00
16010/9/23$ 100.00
16110/9/23$ 360.00
16210/9/23$ 101.00
16310/9/23$ 72.00
16410/9/23$ 75.00
16510/9/23$ 100.00
16610/9/23$ 100.00
16710/9/23$ 180.00
16810/9/23$ 18.00
16910/9/23$ 200.00
17010/9/23$ 100.00
17110/9/23$ 1,000.00
17210/9/23$ 100.00
17310/9/23$ 50.00
17410/9/23$ 350.00
17510/9/23$ 100.00
17610/9/23$ 100.00
17710/9/23$ 126.00
17810/9/23$ 100.00
17910/9/23$ 150.00
18010/9/23$ 100.00
18110/9/23$ 260.00
18210/9/23$ 20.00
18310/9/23$ 180.00
18410/9/23$ 36.00
18510/9/23$ 500.00
18610/9/23$ 100.00
18710/9/23$ 500.00
18810/9/23$ 20.00
18910/9/23$ 500.00
19010/9/23$ 180.00
19110/9/23$ 180.00
19210/9/23$ 100.00
19310/9/23$ 25.00
19410/9/23$ 20.00
19510/9/23$ 100.00
19610/9/23$ 250.00
19710/9/23$ 500.00
19810/9/23$ 1,800.00
19910/9/23$ 10.00
20010/9/23$ 25.00
20110/9/23$ 108.00
20210/9/23$ 1,800.00
20310/9/23$ 100.00
20410/9/23$ 360.00
20510/9/23$ 180.00
20610/9/23$ 180.00
20710/9/23$ 100.00
20810/9/23$ 180.00
20910/9/23$ 20.00
21010/9/23$ 50.00
21110/9/23$ 180.00
21210/9/23$ 260.00
21310/9/23$ 500.00
21410/9/23$ 500.00
21510/9/23$ 50.00
21610/9/23$ 100.00
21710/9/23$ 3,600.00
21810/9/23$ 326.00
21910/9/23$ 500.00
22010/9/23$ 1,000.00
22110/9/23$ 540.00
22210/9/23$ 10,000.00
22310/9/23$ 50.00
22410/9/23$ 100.00
22510/9/23$ 118.00
22610/9/23$ 75.00
22710/9/23$ 180.00
22810/9/23$ 5,000.00
22910/9/23$ 10.00
23010/9/23$ 126.00
23110/9/23$ 20.00
23210/9/23$ 100.00
23310/9/23$ 5,000.00
23410/9/23$ 300.00
23510/9/23$ 100.00
23610/9/23$ 180.00
23710/9/23$ 126.00
23810/9/23$ 100.00
23910/9/23$ 500.00
24010/9/23$ 1,260.00
24110/9/23$ 126.00
24210/9/23$ 70.00
24310/9/23$ 100.00
24410/9/23$ 40.00
24510/9/23$ 50.00
24610/9/23$ 300.00
24710/9/23$ 100.00
24810/9/23$ 100.00
24910/9/23$ 1,800.00
25010/9/23$ 25.00
25110/9/23$ 36.00
25210/9/23$ 100.00
25310/9/23$ 18.00
25410/9/23$ 5,000.00
25510/10/23$ 20,000.00
25610/10/23$ 36.00
25710/10/23$ 1,000.00
25810/10/23$ 500.00
25910/10/23$ 1,500.00
26010/10/23$ 100.00
26110/10/23$ 15.00
26210/10/23$ 250.00
26310/10/23$ 10.00
26410/10/23$ 75.00
26510/10/23$ 200.00
26610/10/23$ 1,000.00
26710/10/23$ 25.00
26810/10/23$ 36.00
26910/10/23$ 260.00
27010/10/23$ 100.00
27110/10/23$ 100.00
27210/10/23$ 250.00
27310/10/23$ 50.00
27410/10/23$ 50.00
27510/10/23$ 50.00
27610/10/23$ 1,800.00
27710/10/23$ 18.00
27810/10/23$ 100.00
27910/10/23$ 360.00
28010/10/23$ 200.00
28110/10/23$ 50.00
28210/10/23$ 500.00
28310/10/23$ 18.00
28410/10/23$ 75.00
28510/10/23$ 250.00
28610/10/23$ 100.00
28710/10/23$ 100.00
28810/10/23$ 50.00
28910/10/23$ 5,000.00
29010/10/23$ 125.00
29110/10/23$ 5.00
29210/10/23$ 100.00
29310/10/23$ 540.00
29410/10/23$ 18.00
29510/10/23$ 100.00
29610/10/23$ 200.00
29710/10/23$ 180.00
29810/10/23$ 5.00
29910/10/23$ 1,800.00
Sheet3
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Try this ?

Range E3
Excel Formula: 
=SUMPRODUCT(1*(YEAR($A$2:$A$299)=YEAR(D3)*(MONTH($A$2:$A$299)=MONTH(D3)))*$B$2:$B$299)

Range E13
Excel Formula: 
=SUMPRODUCT(1*(($B$2:$B$299>D13)*($B$2:$B$299<D12))*$B$2:$B$299)

1716785129116.png
 
Upvote 0
Book1
CDEF
1
2MonthNo. of DonationsTotal Received
310/1/23298$ 159,822.18
411/1/23-$ -
512/1/23-$ -
61/1/24-$ -
72/1/24-$ -
83/1/24-$ -
94/1/24-$ -
10
11
12Gift AmountNo. of DonationsTotal Amount
13Above $100,000100,0000$ -
14from $50,000 to $99,99950,0000$ -
15from $10,000 to $49,99910,0003$ 40,000
16from $5,000 to $9,9995,0008$ 40,000
17from $1,000 to $4,9991,00024$ 34,960
18from $0 to $4,999-263$ 44,862
Sheet3
Cell Formulas
RangeFormula
E3:E9E3=SUM(--(MONTH($A$2:$A$299)=MONTH(D3)))
F3:F9F3=SUM($B$2:$B$299*(MONTH($A$2:$A$299)=MONTH(D3)))
E13E13=COUNTIFS($B$2:$B$299,">="&D13)
F13F13=SUMIFS($B$2:$B$299,$B$2:$B$299,">="&D13)
E14:E18E14=COUNTIFS($B$2:$B$299,">="&D14,$B$2:$B$299,"<"&D13)
F14:F18F14=SUMIFS($B$2:$B$299,$B$2:$B$299,">="&D14,$B$2:$B$299,"<"&D13)
 
Upvote 0
I didn't account when you have multiple years above. You should also consider using Pivot Table. It is made for this.
Book1
CDEF
1
2MonthNo. of DonationsTotal Received
310/1/23298159,822
411/1/230-
512/1/230-
61/1/240-
72/1/240-
83/1/240-
94/1/240-
10
11
12Gift AmountNo. of DonationsTotal Amount
13Above $100,000100,0000$ -
14from $50,000 to $99,99950,0000$ -
15from $10,000 to $49,99910,0003$ 40,000
16from $5,000 to $9,9995,0008$ 40,000
17from $1,000 to $4,9991,00024$ 34,960
18from $0 to $4,999-263$ 44,862
Sheet3
Cell Formulas
RangeFormula
E3:E9E3=COUNTIFS($A$2:$A$299,">="&D3,$A$2:$A$299,"<="&EOMONTH(D3,0))
F3:F9F3=SUM($B$2:$B$299*($A$2:$A$299>=D3)*($A$2:$A$299<=EOMONTH(D3,0)))
E13E13=COUNTIFS($B$2:$B$299,">="&D13)
F13F13=SUMIFS($B$2:$B$299,$B$2:$B$299,">="&D13)
E14:E18E14=COUNTIFS($B$2:$B$299,">="&D14,$B$2:$B$299,"<"&D13)
F14:F18F14=SUMIFS($B$2:$B$299,$B$2:$B$299,">="&D14,$B$2:$B$299,"<"&D13)
 
Upvote 0
You must log in or register to reply here.

Similar threads

SanjayGMusafir
  • Solved
Filtering Dates within the dates and adding corresponding values
Replies
4
Views
111
SanjayGMusafir
SanjayGMusafir
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
271
freeb1893
F
C
  • Solved
Count the number of instances of a word since another word
Replies
6
Views
88
comonand
C
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
286
Lukma
L
W
  • Solved
HELP finding first Date of Clients
Replies
4
Views
194
CephasOz
CephasOz

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

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