SUMIFS but not sure.

TaffyIOM

New Member
Joined
May 18, 2013
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi guys please can you help, I have to show the total of income from memberships for each month either from Cash,Cheque,Card or Bacs each separately, and I'm too much of an amatuer to get a solution on how to do this using the SUMIF, SUMIFS I have tried for three days and keep getting not using enough criteria or to many criteria, I'm new to all this stuff but trying to get my head around it, Thanking you in advance Taffy included is a picture of the excel sheet I'm using (not sure how to upload to share it so you can play with it... again any help in this would be great
)
SDOFA_2020_2021.xlsx
ABCDEFGHIJKLMN
1Membership # Membership Fee & TypeDonationFee TypeDateTotal Each MONTHTotal BACSTotal CASHTotal CHEQUEAMOUNTDIVIDE BY 4VAT @ 20%Total DONATION Exempt of VAT
2001NANANA01/01/2020JAN£0.00£0.00£0.00£55.00
300210.00Cash08/01/2021FEB£0.00£0.00£0.00
400315.00Card01/12/2020MAR£0.00£0.00£0.00
500410.00Cash01/12/2020APR£0.00£0.00£0.00
600515.00Bacs08/12/2020MAY£0.00£0.00£0.00
700610.00Cheque08/01/2021JUN£0.00£0.00£0.00
8007Free5.00Cash08/01/2021JUL£0.00£0.00£0.00
9008Free13/02/2021AUG£0.00£0.00£0.00
1000910.00Card16/03/2021SEP£0.00£0.00£0.00
1101010.00Bacs01/12/2020OCT£0.00£0.00£0.00
12011NOV£0.00£0.00£0.00
13012DEC£0.00£0.00£0.00
14013
15014
16015
1701615.00Card01/12/2020
18017
19018
20019
21020
2202110.00Bacs13/02/2021
2302210.00Bacs13/02/2021
24023
25024
26025
27026
28027
29028
30029Free20.00Bacs16/04/2021
31030
32031
33032
3403310.00Cheque11/02/2021
35034
Sheet2
Cell Formulas
RangeFormula
K2:K13K2=SUM(H2:J2)
L2:M13L2=IF(K2="","",(K2/4))
N2N2=SUM(C2:C101)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1:C11,C14:C1048576Cell Valuecontains "20"textNO
C1:C11,C14:C1048576Cell Valuecontains "10"textNO
C2:C11,C14:C101Cell Valuecontains "5"textNO
B2:C11,B14:C101Cell Valuecontains "15"textNO
B2:C11,B14:C101Cell Valuecontains "10"textNO
B2:C11,B14:C101Cell Valuecontains "Free"textNO
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

sprigelz

Board Regular
Joined
Jan 7, 2016
Messages
54
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
This sums the Membership & Fee Type only. Do you also need to include the Donation column?

Try these for the January Row;
Column H
Excel Formula:
=SUMIFS(B:B,E:E,">="&DATE(2021,1,1),E:E,"<="&DATE(2021,1,31),D:D,"bacs")
Column I
Excel Formula:
=SUMIFS(B:B,E:E,">="&DATE(2021,1,1),E:E,"<="&DATE(2021,1,31),D:D,"cash")
Column J
Excel Formula:
=SUMIFS(B:B,E:E,">="&DATE(2021,1,1),E:E,"<="&DATE(2021,1,31),D:D,"cheque")

Change the numbers within both Date functions to associate Feb, Mar, Apr, etc...
Date formula - Date(year,month,day)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,896
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1Membership # Membership Fee & TypeDonationFee TypeDateTotal Each MONTHTotal BACSTotal CASHTotal CHEQUEAMOUNTDIVIDE BY 4VAT @ 20%Total DONATION Exempt of VAT
21NANANA01/01/202001/01/2021010102051.2525
3210Cash08/01/202101/02/202120010307.51.875
4315Card01/12/202001/03/2021000000
5410Cash01/12/202001/04/2021000000
6515Bacs08/12/202001/05/2021000000
7610Cheque08/01/202101/06/2021000000
87Free5Cash08/01/202101/07/2021000000
98Free13/02/202101/08/2021000000
10910Card16/03/202101/09/2021000000
111010Bacs01/12/202001/10/2021000000
121101/11/2021000000
131201/12/2021000000
1413
1514
1615
171615Card01/12/2020
1817
1918
2019
2120
222110Bacs13/02/2021
232210Bacs13/02/2021
2423
2524
2625
2726
2827
2928
3029Free20Bacs16/04/2021
3130
3231
3332
343310Cheque11/02/2021
3534
Data
Cell Formulas
RangeFormula
H2:H13H2=SUMIFS($B:$B,$D:$D,"Bacs",$E:$E,">="&$G2,$E:$E,"<="&EOMONTH($G2,0))
I2:I13I2=SUMIFS($B:$B,$D:$D,"Cash",$E:$E,">="&$G2,$E:$E,"<="&EOMONTH($G2,0))
J2:J13J2=SUMIFS($B:$B,$D:$D,"Cheque",$E:$E,">="&$G2,$E:$E,"<="&EOMONTH($G2,0))
K2:K13K2=SUM(H2:J2)
L2:M13L2=IF(K2="","",(K2/4))
N2N2=SUM(C2:C101)
 

TaffyIOM

New Member
Joined
May 18, 2013
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
This sums the Membership & Fee Type only. Do you also need to include the Donation column?

Try these for the January Row;
Column H
Excel Formula:
=SUMIFS(B:B,E:E,">="&DATE(2021,1,1),E:E,"<="&DATE(2021,1,31),D:D,"bacs")
Column I
Excel Formula:
=SUMIFS(B:B,E:E,">="&DATE(2021,1,1),E:E,"<="&DATE(2021,1,31),D:D,"cash")
Column J
Excel Formula:
=SUMIFS(B:B,E:E,">="&DATE(2021,1,1),E:E,"<="&DATE(2021,1,31),D:D,"cheque")

Change the numbers within both Date functions to associate Feb, Mar, Apr, etc...
Date formula - Date(year,month,day)
Thank you I will try these, I don't need to calculate donations as we don't pay vat on them.
 

Forum statistics

Threads
1,136,284
Messages
5,674,841
Members
419,530
Latest member
undisclosed

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
Top