I want to produce summary data from something like the following:
Col A – Col B
1 – 15
4 – 8
7 – 20
9 – 5
Let’s say the ranges I want for my summary data is 1-5 and 6-10 for column A, summing on colB. So, how would I produce the following code?
Range | 1-5, 6-10
Sum(B) | 23, 25
The “query” function will break down everything (i.e. 1-5 becomes 1, 2, 3, 4, 5)
New contributor
Brian Theung is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Slightly expanding Michal’s, using Excel 2021 and higher:
Enter in the first cell, E2 in the example
<code>=LET(
a, A1:A4,
b, B1:B4,
range, D2:D3,
from, LEFT(range, FIND("-", range) - 1),
to, RIGHT(range, LEN(range) - FIND("-", range)),
SUMIFS(b, a, ">=" & from, a, "<=" & to)
)
</code>
<code>=LET(
a, A1:A4,
b, B1:B4,
range, D2:D3,
from, LEFT(range, FIND("-", range) - 1),
to, RIGHT(range, LEN(range) - FIND("-", range)),
SUMIFS(b, a, ">=" & from, a, "<=" & to)
)
</code>
=LET(
a, A1:A4,
b, B1:B4,
range, D2:D3,
from, LEFT(range, FIND("-", range) - 1),
to, RIGHT(range, LEN(range) - FIND("-", range)),
SUMIFS(b, a, ">=" & from, a, "<=" & to)
)
The simplest way is to use SUMIFS:
=SUMIFS(B:B, A:A, ">=1", A:A, "<=5"), etc