Sum multiple rows using columns as criteria

Question

I'm trying to sum multiple rows using column headers as criteria but keep coming up blank. In this table, the range that needs to be summed is D2:F4 but ultimately I'll be summing arbitrary, contiguous date ranges.

All help greatly appreciated. Thanks! (Sorry no rep yet to post pics)

To return data from the current week (based on Rory's solution):

=SUMPRODUCT((B1:1>=(TODAY()-WEEKDAY(TODAY(),2)+1))*(B1:1<=(TODAY()-WEEKDAY(TODAY(),2)+5))*(B2:4))

Use SUMPRODUCT - eg =SUMPRODUCT((B1:Z1>=DATE(2021,9,1))*(B1:Z1<=DATE(2021,9,30))*(B2:Z4)) — Rory
– Rory, Commented Sep 28, 2021 at 11:04
It's beautiful. Thanks... If only my little brain could understand how multiplication leads to a sum!!! Put your answer below so I can accept it please? — Vrienden van Andries
– Vrienden van Andries, Commented Sep 28, 2021 at 11:31

Rory · Accepted Answer · 2021-09-28 11:43:49Z

1

You can use SUMPRODUCT for this - eg:

=SUMPRODUCT((B1:Z1>=DATE(2021,9,1))*(B1:Z1<=DATE(2021,9,30))*(B2:Z4))

to sum data for September of this year.

answered Sep 28, 2021 at 11:43

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

1 Comment

Vrienden van Andries Over a year ago

Thanks Rory. Added another formula to the orig question that returns the sum for the current week.

Collectives™ on Stack Overflow

Sum multiple rows using columns as criteria

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related