sum help with multiple dates

[yogi 6480]

I am trying to figure out how to the sum numbers from, J7, J14, J23, J30 (class size) etc. as long as the dates in B5, B12, B21, and B28 (class dates)are equal to or have passed from today. I have used this formula =IF(TODAY()-B5, >=0,J7, "") that works for one cell with one date. I am trying to use it for multiple class starts dates with multiple class sizes.



Class start date 1 Sep 20 (cell B5) total class number 20 (cell J7)

Class start date 1 Oct 20 (cell B12) total class number 10 (cell J14)

Class start date 1 Nov 20 (cell B21) total class number 20 (cell J3)

Class start date 1 Dec 20 (cell B28) total class number 10 (cell J30)

 

[Eric W]

Welcome to the MrExcel forum!

Looking at disjoint ranges is hard enough, but you can sometimes work with them if the various cells are in a regular pattern. In your case, B5 and B12 are 7 rows apart, B12 and B21 are 9 rows apart, and B21 and B28 are 7 rows apart again. No useable pattern. There might also be a way if the dates and/or class size cells are identified by headers in column A and I. It would help if you could show an example of your worksheet. Look at the XL2BB tool in the response box, it shows a nice way to show such an example. I'll use it below.

Without some kind of pattern to use, the only way to do this is by explicitly listing the desired cells in an array. Consider:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Formula:	30
3
4
5		9/1/2020
6
7										20
8
9
10
11
12		10/1/2020
13
14										10
15
16
17
18
19
20
21		11/1/2020
22
23										20
24
25
26
27
28		12/1/2020
29
30										10
Sheet10

Cell Formulas
Range	Formula
C2	C2	=SUM(SUMIFS(OFFSET(J1,{6,13,22,29},0),OFFSET(B1,{4,11,20,27},0),"<="&TODAY()))

But really, this does not scale well. Let us know if there's something else we can use to figure out which cells to use.