Sumsif with multiple conditions

roxdrob

New Member
Joined
Mar 2, 2017
Messages
24
Hi. I have a spreadsheet set up as a calendar. I have 3 columns per date square: Amount, Description, and if cleared (checkmark). There are 5 rows in each square. The top 3 are for debits and the bottom two are for credits. The weekends are excluded. I would like to do a formula that searches across the 5 days, top 3 rows, and looks for an "x" in the "cleared" column, and if found, sum the amounts. If there are multiple debit entries that have cleared, all those amounts would be summed but not the ones without the "x". Any ideas on how to accomplish that?

2021BillsCalendar(New).xlsx
ABCDEFGHIJKLMNOPQRSTUV
1July 2021
2
3
4
5
6
7
8 Sum of Debits Sum of Debts not cleared
9SundayMondayTuesdayWednesdayThursdayFridaySaturday Sum of Credits Sum of Credits
10AmtDescriptionÖAmtDescriptionÖAmtDescriptionÖAmtDescriptionÖAmtDescriptionÖDifferenceDifference
1127282930123-
12-
13-
14
15
16
1745678910212.50Should read 150
18100Debit 1x63Debit 2116.00Should read 116
1950Debit 3x328.50
20
2148Credit 1
2268Credit 2
2311121314151617-
24-
25-
26
27
28
2918192021222324-
30-
31-
32
33
34
3525262728293031-
36-
37-
38
39
40
4112Notes-
42-
43
44
45
46
Jul
Cell Formulas
RangeFormula
A1A1=DATE(DateEntry!C18,DateEntry!C20+6,1)
A9,C9,F9,I9,L9,O9,R9A9=A11
A11A11=$A$1-(WEEKDAY($A$1,1)-(DateEntry!start_day-1))-IF((WEEKDAY($A$1,1)-(DateEntry!start_day-1))<=0,7,0)+1
C11,C41,C35,C29,C23,C17C11=A11+1
F11,R35,O35,L35,I35,F35,R29,O29,L29,I29,F29,R23,O23,L23,I23,F23,R17,O17,L17,I17,F17,R11,O11,L11,I11F11=C11+1
T11,T41,T35,T29,T23,T17T11=SUM(C12:Q14)
T12,T42,T36,T30,T24,T18T12=SUM(C15:Q16)
T13,T37,T31,T25,T19T13=T11+T12
A17,A41,A35,A29,A23A17=R11+1
Named Ranges
NameRefers ToCells
Print_Area=Jul!$A$1:$S$46A11
DateEntry!start_day=DateEntry!$C$24A11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O35Expression=MONTH(O35)<>MONTH($A$1)textNO
O35Expression=OR(WEEKDAY(O35,1)=1,WEEKDAY(O35,1)=7)textNO
O29Expression=MONTH(O29)<>MONTH($A$1)textNO
O29Expression=OR(WEEKDAY(O29,1)=1,WEEKDAY(O29,1)=7)textNO
O23Expression=MONTH(O23)<>MONTH($A$1)textNO
O23Expression=OR(WEEKDAY(O23,1)=1,WEEKDAY(O23,1)=7)textNO
O17Expression=MONTH(O17)<>MONTH($A$1)textNO
O17Expression=OR(WEEKDAY(O17,1)=1,WEEKDAY(O17,1)=7)textNO
O11Expression=MONTH(O11)<>MONTH($A$1)textNO
O11Expression=OR(WEEKDAY(O11,1)=1,WEEKDAY(O11,1)=7)textNO
L11Expression=MONTH(L11)<>MONTH($A$1)textNO
L11Expression=OR(WEEKDAY(L11,1)=1,WEEKDAY(L11,1)=7)textNO
L17Expression=MONTH(L17)<>MONTH($A$1)textNO
L17Expression=OR(WEEKDAY(L17,1)=1,WEEKDAY(L17,1)=7)textNO
L23Expression=MONTH(L23)<>MONTH($A$1)textNO
L23Expression=OR(WEEKDAY(L23,1)=1,WEEKDAY(L23,1)=7)textNO
L29Expression=MONTH(L29)<>MONTH($A$1)textNO
L29Expression=OR(WEEKDAY(L29,1)=1,WEEKDAY(L29,1)=7)textNO
L35Expression=MONTH(L35)<>MONTH($A$1)textNO
L35Expression=OR(WEEKDAY(L35,1)=1,WEEKDAY(L35,1)=7)textNO
I35Expression=MONTH(I35)<>MONTH($A$1)textNO
I35Expression=OR(WEEKDAY(I35,1)=1,WEEKDAY(I35,1)=7)textNO
I29Expression=MONTH(I29)<>MONTH($A$1)textNO
I29Expression=OR(WEEKDAY(I29,1)=1,WEEKDAY(I29,1)=7)textNO
I23Expression=MONTH(I23)<>MONTH($A$1)textNO
I23Expression=OR(WEEKDAY(I23,1)=1,WEEKDAY(I23,1)=7)textNO
I17Expression=MONTH(I17)<>MONTH($A$1)textNO
I17Expression=OR(WEEKDAY(I17,1)=1,WEEKDAY(I17,1)=7)textNO
I11Expression=MONTH(I11)<>MONTH($A$1)textNO
I11Expression=OR(WEEKDAY(I11,1)=1,WEEKDAY(I11,1)=7)textNO
F35Expression=MONTH(F35)<>MONTH($A$1)textNO
F35Expression=OR(WEEKDAY(F35,1)=1,WEEKDAY(F35,1)=7)textNO
F29Expression=MONTH(F29)<>MONTH($A$1)textNO
F29Expression=OR(WEEKDAY(F29,1)=1,WEEKDAY(F29,1)=7)textNO
F23Expression=MONTH(F23)<>MONTH($A$1)textNO
F23Expression=OR(WEEKDAY(F23,1)=1,WEEKDAY(F23,1)=7)textNO
F17Expression=MONTH(F17)<>MONTH($A$1)textNO
F17Expression=OR(WEEKDAY(F17,1)=1,WEEKDAY(F17,1)=7)textNO
F11Expression=MONTH(F11)<>MONTH($A$1)textNO
F11Expression=OR(WEEKDAY(F11,1)=1,WEEKDAY(F11,1)=7)textNO
C41Expression=MONTH(C41)<>MONTH($A$1)textNO
C41Expression=OR(WEEKDAY(C41,1)=1,WEEKDAY(C41,1)=7)textNO
C35Expression=MONTH(C35)<>MONTH($A$1)textNO
C35Expression=OR(WEEKDAY(C35,1)=1,WEEKDAY(C35,1)=7)textNO
C29Expression=MONTH(C29)<>MONTH($A$1)textNO
C29Expression=OR(WEEKDAY(C29,1)=1,WEEKDAY(C29,1)=7)textNO
C23Expression=MONTH(C23)<>MONTH($A$1)textNO
C23Expression=OR(WEEKDAY(C23,1)=1,WEEKDAY(C23,1)=7)textNO
C17Expression=MONTH(C17)<>MONTH($A$1)textNO
C17Expression=OR(WEEKDAY(C17,1)=1,WEEKDAY(C17,1)=7)textNO
R11,R17,R23,R29,R35Expression=MONTH(R11)<>MONTH($A$1)textNO
R11,R17,R23,R29,R35Expression=OR(WEEKDAY(R11,1)=1,WEEKDAY(R11,1)=7)textNO
A11,C11,A17,A23,A29,A35,A41Expression=MONTH(A11)<>MONTH($A$1)textNO
A11,C11,A17,A23,A29,A35,A41Expression=OR(WEEKDAY(A11,1)=1,WEEKDAY(A11,1)=7)textNO
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,065
Office Version
  1. 365
  2. 2010
At first blush, the 150 wasn't too difficult to come up with because there are only 5 days being considered.
There's probably a shorter/better method, but I didn't think of it initially.
The credits can be done with an Aggregate.

Here's part of the calendar:

Book19
ABCDEFGHIJKLMNOPQRSTUV
1127282930123
12
13
14
15
16
1745678910150.00Should read 150
18100Debit 1x63Debit 2116.00Should read 116
1950Debit 3x266.00
20
2148Credit 1
2268Credit 2
2311121314151617
Sheet1
Cell Formulas
RangeFormula
S17S17=SUM(IF("x"=D18:D20,B18:B20,0)+IF("x"=G18:G20,E18:E20,0)+IF("x"=J18:J20,H18:H20,0)+IF("x"=M18:M20,K18:K20,0)+IF("x"=P18:P20,N18:N20,0))
S18S18=AGGREGATE(9,6,C21:P22)
S19S19=SUM(S17:S18)
 

roxdrob

New Member
Joined
Mar 2, 2017
Messages
24
At first blush, the 150 wasn't too difficult to come up with because there are only 5 days being considered.
There's probably a shorter/better method, but I didn't think of it initially.
The credits can be done with an Aggregate.

Here's part of the calendar:

Book19
ABCDEFGHIJKLMNOPQRSTUV
1127282930123
12
13
14
15
16
1745678910150.00Should read 150
18100Debit 1x63Debit 2116.00Should read 116
1950Debit 3x266.00
20
2148Credit 1
2268Credit 2
2311121314151617
Sheet1
Cell Formulas
RangeFormula
S17S17=SUM(IF("x"=D18:D20,B18:B20,0)+IF("x"=G18:G20,E18:E20,0)+IF("x"=J18:J20,H18:H20,0)+IF("x"=M18:M20,K18:K20,0)+IF("x"=P18:P20,N18:N20,0))
S18S18=AGGREGATE(9,6,C21:P22)
S19S19=SUM(S17:S18)

Thanks for your response. I'm not able to get the first formula to work for me, unfortunately. For some reason, it's picking up the Sunday section too. Why is that?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,065
Office Version
  1. 365
  2. 2010
I don't see how it picks up that section as it begins checking in the D column which seems to be the first column for Monday.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=SUMIFS($C$18:$O$20,$E$18:$Q$20,"x")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,803
Messages
5,677,823
Members
419,722
Latest member
Rizzol

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