Sum columns by multiple criteria in excel

Question

I'm having trouble finding a short formula when adding columns in excel.

	A	B	C	D	E	F	G	H
1	Monday	Monday	Tuesday	Tuesday	Wednesday	Wednesday	Thursday	Thursday
2	Day	Night	Day	Night	Day	Night	Day	Night
3	10	5	0	10	0	5	10	10

I want to sum only the night of the days whose day number is zero. You can see the long formula below.

=IF(A3=0;B3;0)+IF(C3=0;D3;0)+IF(E3=0;F3;0)+IF(G3=0;H3;0)

The result should be 15. Since there are no numbers during the daytime on Tuesdays and Wednesdays, it should be summing night numbers. It is short for 4 days but very long to write this formula for a month. Thank you in advance.

Harun24hr · Accepted Answer · 2023-08-10 08:10:27Z

1

Try SUMPRODUCT().

=SUMPRODUCT((A4:I4=0)*(A3:I3="Day")*(B4:J4))

Or SUMPRODUCT().

=SUM(FILTER(B4:J4,(A3:I3="Day")*(A4:I4=0)))

answered Aug 10, 2023 at 8:10

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum columns by multiple criteria in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related