# Sumsif with multiple conditions

#### roxdrob

##### New Member
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
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

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

#### kweaver

##### Well-known Member
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
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
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
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
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

Excel Formula:
``=SUMIFS(\$C\$18:\$O\$20,\$E\$18:\$Q\$20,"x")``

#### roxdrob

##### New Member
Excel Formula:
``=SUMIFS(\$C\$18:\$O\$20,\$E\$18:\$Q\$20,"x")``
That's perfect! Thank you!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
67
Replies
10
Views
404

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.

### Which adblocker are you using?

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

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