Sumsif with multiple conditions

roxdrob · May 28, 2021

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

July 2021

2

3

4

5

6

7

8

Sum of Debits

Sum of Debts not cleared

9

Sum of Credits

10

Amt

Description

Ö

Amt

Description

Ö

Amt

Description

Ö

Amt

Description

Ö

Amt

Description

Ö

Difference

11

12

13

14

15

16

17

Should read 150

18

100

Debit 1

x

63

Debit 2

116.00

Should read 116

19

50

Debit 3

x

328.50

20

21

48

Credit 1

22

68

Credit 2

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

Notes

42

43

44

45

46

Jul

Cell Formulas
Range	Formula
A1	A1	=DATE(DateEntry!C18,DateEntry!C20+6,1)
A9,C9,F9,I9,L9,O9,R9	A9	=A11
A11	A11	=$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,C17	C11	=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,I11	F11	=C11+1
T11,T41,T35,T29,T23,T17	T11	=SUM(C12:Q14)
T12,T42,T36,T30,T24,T18	T12	=SUM(C15:Q16)
T13,T37,T31,T25,T19	T13	=T11+T12
A17,A41,A35,A29,A23	A17	=R11+1

Named Ranges
Name	Refers To	Cells
Print_Area	=Jul!$A$1:$S$46	A11
DateEntry!start_day	=DateEntry!$C$24	A11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O35	Expression	=MONTH(O35)<>MONTH($A$1)	text	NO
O35	Expression	=OR(WEEKDAY(O35,1)=1,WEEKDAY(O35,1)=7)	text	NO
O29	Expression	=MONTH(O29)<>MONTH($A$1)	text	NO
O29	Expression	=OR(WEEKDAY(O29,1)=1,WEEKDAY(O29,1)=7)	text	NO
O23	Expression	=MONTH(O23)<>MONTH($A$1)	text	NO
O23	Expression	=OR(WEEKDAY(O23,1)=1,WEEKDAY(O23,1)=7)	text	NO
O17	Expression	=MONTH(O17)<>MONTH($A$1)	text	NO
O17	Expression	=OR(WEEKDAY(O17,1)=1,WEEKDAY(O17,1)=7)	text	NO
O11	Expression	=MONTH(O11)<>MONTH($A$1)	text	NO
O11	Expression	=OR(WEEKDAY(O11,1)=1,WEEKDAY(O11,1)=7)	text	NO
L11	Expression	=MONTH(L11)<>MONTH($A$1)	text	NO
L11	Expression	=OR(WEEKDAY(L11,1)=1,WEEKDAY(L11,1)=7)	text	NO
L17	Expression	=MONTH(L17)<>MONTH($A$1)	text	NO
L17	Expression	=OR(WEEKDAY(L17,1)=1,WEEKDAY(L17,1)=7)	text	NO
L23	Expression	=MONTH(L23)<>MONTH($A$1)	text	NO
L23	Expression	=OR(WEEKDAY(L23,1)=1,WEEKDAY(L23,1)=7)	text	NO
L29	Expression	=MONTH(L29)<>MONTH($A$1)	text	NO
L29	Expression	=OR(WEEKDAY(L29,1)=1,WEEKDAY(L29,1)=7)	text	NO
L35	Expression	=MONTH(L35)<>MONTH($A$1)	text	NO
L35	Expression	=OR(WEEKDAY(L35,1)=1,WEEKDAY(L35,1)=7)	text	NO
I35	Expression	=MONTH(I35)<>MONTH($A$1)	text	NO
I35	Expression	=OR(WEEKDAY(I35,1)=1,WEEKDAY(I35,1)=7)	text	NO
I29	Expression	=MONTH(I29)<>MONTH($A$1)	text	NO
I29	Expression	=OR(WEEKDAY(I29,1)=1,WEEKDAY(I29,1)=7)	text	NO
I23	Expression	=MONTH(I23)<>MONTH($A$1)	text	NO
I23	Expression	=OR(WEEKDAY(I23,1)=1,WEEKDAY(I23,1)=7)	text	NO
I17	Expression	=MONTH(I17)<>MONTH($A$1)	text	NO
I17	Expression	=OR(WEEKDAY(I17,1)=1,WEEKDAY(I17,1)=7)	text	NO
I11	Expression	=MONTH(I11)<>MONTH($A$1)	text	NO
I11	Expression	=OR(WEEKDAY(I11,1)=1,WEEKDAY(I11,1)=7)	text	NO
F35	Expression	=MONTH(F35)<>MONTH($A$1)	text	NO
F35	Expression	=OR(WEEKDAY(F35,1)=1,WEEKDAY(F35,1)=7)	text	NO
F29	Expression	=MONTH(F29)<>MONTH($A$1)	text	NO
F29	Expression	=OR(WEEKDAY(F29,1)=1,WEEKDAY(F29,1)=7)	text	NO
F23	Expression	=MONTH(F23)<>MONTH($A$1)	text	NO
F23	Expression	=OR(WEEKDAY(F23,1)=1,WEEKDAY(F23,1)=7)	text	NO
F17	Expression	=MONTH(F17)<>MONTH($A$1)	text	NO
F17	Expression	=OR(WEEKDAY(F17,1)=1,WEEKDAY(F17,1)=7)	text	NO
F11	Expression	=MONTH(F11)<>MONTH($A$1)	text	NO
F11	Expression	=OR(WEEKDAY(F11,1)=1,WEEKDAY(F11,1)=7)	text	NO
C41	Expression	=MONTH(C41)<>MONTH($A$1)	text	NO
C41	Expression	=OR(WEEKDAY(C41,1)=1,WEEKDAY(C41,1)=7)	text	NO
C35	Expression	=MONTH(C35)<>MONTH($A$1)	text	NO
C35	Expression	=OR(WEEKDAY(C35,1)=1,WEEKDAY(C35,1)=7)	text	NO
C29	Expression	=MONTH(C29)<>MONTH($A$1)	text	NO
C29	Expression	=OR(WEEKDAY(C29,1)=1,WEEKDAY(C29,1)=7)	text	NO
C23	Expression	=MONTH(C23)<>MONTH($A$1)	text	NO
C23	Expression	=OR(WEEKDAY(C23,1)=1,WEEKDAY(C23,1)=7)	text	NO
C17	Expression	=MONTH(C17)<>MONTH($A$1)	text	NO
C17	Expression	=OR(WEEKDAY(C17,1)=1,WEEKDAY(C17,1)=7)	text	NO
R11,R17,R23,R29,R35	Expression	=MONTH(R11)<>MONTH($A$1)	text	NO
R11,R17,R23,R29,R35	Expression	=OR(WEEKDAY(R11,1)=1,WEEKDAY(R11,1)=7)	text	NO
A11,C11,A17,A23,A29,A35,A41	Expression	=MONTH(A11)<>MONTH($A$1)	text	NO
A11,C11,A17,A23,A29,A35,A41	Expression	=OR(WEEKDAY(A11,1)=1,WEEKDAY(A11,1)=7)	text	NO

kweaver · May 28, 2021

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

11

27

28

29

30

1

2

3

12

13

14

15

16

17

4

5

6

7

8

9

10

150.00

Should read 150

18

100

Debit 1

x

63

Debit 2

116.00

Should read 116

19

50

Debit 3

x

266.00

20

21

48

Credit 1

22

68

Credit 2

23

11

12

13

14

15

16

17

Sheet1

Cell Formulas
Range	Formula
S17	S17	=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))
S18	S18	=AGGREGATE(9,6,C21:P22)
S19	S19	=SUM(S17:S18)

roxdrob · May 28, 2021

kweaver said:
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
A B C D E F G H I J K L M N O P Q R S T U V
11 27 28 29 30 1 2 3
12
13
14
15
16
17 4 5 6 7 8 9 10 150.00 Should read 150
18 100 Debit 1 x 63 Debit 2 116.00 Should read 116
19 50 Debit 3 x 266.00
20
21 48 Credit 1
22 68 Credit 2
23 11 12 13 14 15 16 17
Sheet1
Cell Formulas
Range Formula
S17 S17 =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))
S18 S18 =AGGREGATE(9,6,C21:P22)
S19 S19 =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 · May 29, 2021

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 · May 29, 2021

How about

Excel Formula:

=SUMIFS($C$18:$O$20,$E$18:$Q$20,"x")

roxdrob · Jun 1, 2021

Fluff said:
How about

Excel Formula:

=SUMIFS($C$18:$O$20,$E$18:$Q$20,"x")

That's perfect! Thank you!!

Fluff · Jun 1, 2021

You're welcome & thanks for the feedback.

Sumsif with multiple conditions

roxdrob

New Member

Excel Facts

kweaver

Well-known Member

roxdrob

New Member

kweaver

Well-known Member

Fluff

MrExcel MVP, Moderator

roxdrob

New Member

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

Sumsif with multiple conditions

roxdrob

New Member

Excel Facts

kweaver

Well-known Member

roxdrob

New Member

kweaver

Well-known Member

Fluff

MrExcel MVP, Moderator

roxdrob

New Member

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock